DAX (Comparison Today vs Last Year Same Day)

sureshsonti · Dec 17, 2018

These are the fields in my Data Set. I am trying to avoid filters on the report. I have attached a small sample data set

Order Date
Year
Month
Units Sold

1. I want to extract Units Sold as of today (Current Day) based on the Order Date
2. I want to extract Units Sold as of same day (Last Year) based on the Order Date

Want to compare Units Sold Today and Last Year same Day using DAX formula. Trying to avoid filters on the report

Example 12/17/2018 and 12/17/2017

Thanks for your help

Chihiro · Dec 17, 2018

Since you don't have any dimension table...

Current day sales
=CALCULATE(SUM([Units Sold]),FILTER(ALL('Table'),[Order Date]=Today()))

But typically, you'd want full day of data to compare (a day behind). If that's the case...
=CALCULATE(SUM([Units Sold]),FILTER(ALL('Table'),[Order Date]=Today()-1))

To compare to same date (irregardless of weekday)...
=CALCULATE(SUM([Units Sold]),FILTER(ALL('Table'),[Order Date]=Date(year(today())-1,Month(today()),Day(today()))))

However, do note that these type of calculation will fail on leap day. Also, unless your business does not have weekly pattern, it will not be fair comparison (ex: comparing Monday's data to Friday's data may skew comparison).

There are many ways to deal with this sort of time intelligence. Such as setting up business specific calendar (ex: Broadcast Calendar), to align each year's week to close approximation of previous year and comparing based on intra-week pattern. Or aligning each year by first Monday and dropping extras. But it isn't clear from your sample and description what you may need for your business.

sureshsonti · Dec 17, 2018

Chihiro said:
Since you don't have any dimension table...

Current day sales
=CALCULATE(SUM([Units Sold]),FILTER(ALL('Table'),[Order Date]=Today()))

But typically, you'd want full day of data to compare (a day behind). If that's the case...
=CALCULATE(SUM([Units Sold]),FILTER(ALL('Table'),[Order Date]=Today()-1))

To compare to same date (irregardless of weekday)...
=CALCULATE(SUM([Units Sold]),FILTER(ALL('Table'),[Order Date]=Date(year(today())-1,Month(today()),Day(today()))))

However, do note that these type of calculation will fail on leap day. Also, unless your business does not have weekly pattern, it will not be fair comparison (ex: comparing Monday's data to Friday's data may skew comparison).

There are many ways to deal with this sort of time intelligence. Such as setting up business specific calendar (ex: Broadcast Calendar), to align each year's week to close approximation of previous year and comparing based on intra-week pattern. Or aligning each year by first Monday and dropping extras. But it isn't clear from your sample and description what you may need for your business.

Chihiro,
Thanks for your quick response. I will give it a try.

I am doing a direct query from a SQL Server database and not sure if I can create a dimension table.

Thanks for your help

sureshsonti · Dec 17, 2018

Chihiro said:
Since you don't have any dimension table...

Current day sales
=CALCULATE(SUM([Units Sold]),FILTER(ALL('Table'),[Order Date]=Today()))

But typically, you'd want full day of data to compare (a day behind). If that's the case...
=CALCULATE(SUM([Units Sold]),FILTER(ALL('Table'),[Order Date]=Today()-1))

To compare to same date (irregardless of weekday)...
=CALCULATE(SUM([Units Sold]),FILTER(ALL('Table'),[Order Date]=Date(year(today())-1,Month(today()),Day(today()))))

However, do note that these type of calculation will fail on leap day. Also, unless your business does not have weekly pattern, it will not be fair comparison (ex: comparing Monday's data to Friday's data may skew comparison).

There are many ways to deal with this sort of time intelligence. Such as setting up business specific calendar (ex: Broadcast Calendar), to align each year's week to close approximation of previous year and comparing based on intra-week pattern. Or aligning each year by first Monday and dropping extras. But it isn't clear from your sample and description what you may need for your business.

Chihiro,
The Current Day Sales formula didnt extract any values. Thanks

Chihiro · Dec 17, 2018

You can create calendar dimension in several ways.

1. In SQL using Stored Proc and Tally table (incremental int value table with single column). You can read up on T-SQL tally table in link below.
http://www.sqlservercentral.com/blogs/dwainsql/2014/03/27/tally-tables-in-t-sql/

It is one of the fastest method to create dynamic calendar dimension table in T-SQL.

2. Use DAX to create dynamic calendar table. I sometimes use this method, when I can't readily utilize MSSQL server. You can find example in link, though you may want to trim it down to what's needed.
https://www.sqlbi.com/articles/reference-date-table-in-dax-and-power-bi/

3. Use PowerQuery's M function to create dynamic calendar. I can't remember the thread, but I've posted in the forum about this.

As for your issue. Can't really help you without more detail. I had no issue with your sample data.

sureshsonti · Dec 17, 2018

Chihiro said:
You can create calendar dimension in several ways.

1. In SQL using Stored Proc and Tally table (incremental int value table with single column). You can read up on T-SQL tally table in link below.
http://www.sqlservercentral.com/blogs/dwainsql/2014/03/27/tally-tables-in-t-sql/

It is one of the fastest method to create dynamic calendar dimension table in T-SQL.

2. Use DAX to create dynamic calendar table. I sometimes use this method, when I can't readily utilize MSSQL server. You can find example in link, though you may want to trim it down to what's needed.
https://www.sqlbi.com/articles/reference-date-table-in-dax-and-power-bi/

3. Use PowerQuery's M function to create dynamic calendar. I can't remember the thread, but I've posted in the forum about this.

As for your issue. Can't really help you without more detail. I had no issue with your sample data.

View attachment 57054

Chihiro,
This is what I am getting when I used the Current Day Sales formula. Also your screen shot shows Current Sales at 770 for 12/17/2018. But I get a total of $1650 for 12/17/2018. My data set is in excel for now and I dont have any sales for the Gonzales Plant. Not sure why Power BI is picking up that plant also.

Thanks for input.

Chihiro · Dec 17, 2018

Remove All() from formula

sureshsonti · Dec 17, 2018

Chihiro said:
Remove All() from formula

That worked. Thanks for your help

sureshsonti · Dec 17, 2018

Chihiro said:
Remove All() from formula

Chihiro,
I was able to get Month To Date of the current year. How can I get the MTD of Last Year. Its the same Data Set. Please ignore the Fields in the screenshot

Thanks for your help

Nebu · Dec 18, 2018

Hi:

May be
For Previous Year

Code:

PY MTD =
IF(
    ISFILTERED('Sheet1'[Order Date]),
    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
    TOTALMTD(SUM('Sheet1'[Units Sold]), SAMEPERIODLASTYEAR('Sheet1'[Order Date].[Date])
))

For Current Year

Code:

PY MTD =
IF(
    ISFILTERED('Sheet1'[Order Date]),
    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
    TOTALMTD(SUM('Sheet1'[Units Sold]), SAMEPERIODLASTYEAR('Sheet1'[Order Date].[Date])
))

Thanks

Chihiro · Dec 18, 2018

If you have date dimension table, then go with Nebu's method.

If not, or if using direct query mode, you can try....
=Calculate(SUM([Units Sold]),FILTER('Table',[Month]=FORMAT(Today(),"mmm")&&[Year]=Year(Today())-1&&Day([Order Date])<Today()))

Edit: More accurately... last argument should be...
Day([Order Date])<=Day(Today())

sureshsonti · Dec 18, 2018

Chihiro said:
If you have date dimension table, then go with Nebu's method.

If not, or if using direct query mode, you can try....
=Calculate(SUM([Units Sold]),FILTER('Table',[Month]=FORMAT(Today(),"mmm")&&[Year]=Year(Today())-1&&Day([Order Date])<Today()))

View attachment 57080

Edit: More accurately... last argument should be...
Day([Order Date])<=Day(Today())

I don't have date dimension.
I found this online for the Current Year MTD

CURRENT_MONTH = IF(YEAR('Table'[order_date]) = YEAR(TODAY()) && MONTH('Table'[order_date]) = MONTH(TODAY()), 1, 0).

I used it as a visual level filter in Power BI visual and it worked. Trying to get the Previous Year MTD.

Chihiro, I will try yours now

sureshsonti · Dec 18, 2018

Chihiro said:
If you have date dimension table, then go with Nebu's method.

If not, or if using direct query mode, you can try....
=Calculate(SUM([Units Sold]),FILTER('Table',[Month]=FORMAT(Today(),"mmm")&&[Year]=Year(Today())-1&&Day([Order Date])<Today()))

View attachment 57080

Edit: More accurately... last argument should be...
Day([Order Date])<=Day(Today())

Chihiro,

I used your formula:
CALCULATE(SUM('Table'[Units Sold]), FILTER('Table',[Month] = FORMAT(TODAY(),"mmm") && [Year] = YEAR(TODAY())-1 && DAY([Order Date] <= DAY(TODAY())))).

I get the below result. What am I doing wrong. I see your screen shot and thats the answer I am looking for.

Thanks for your help

Chihiro · Dec 18, 2018

You have closing parenthesis at wrong position for last arg.

Should be...
=Calculate(SUM([Units Sold]),FILTER('Table',[Month]=FORMAT(Today(),"mmm")&&[Year]=Year(Today())-1&&Day([Order Date])<=Day(Today())))

sureshsonti · Dec 18, 2018

Chihiro said:
You have closing parenthesis at wrong position for last arg.

Should be...
=Calculate(SUM([Units Sold]),FILTER('Table',[Month]=FORMAT(Today(),"mmm")&&[Year]=Year(Today())-1&&Day([Order Date])<=Day(Today())))

Thanks Chirhiro. That worked. Thanks for your help again.

sureshsonti · Dec 20, 2018

Chihiro said:
You have closing parenthesis at wrong position for last arg.

Should be...
=Calculate(SUM([Units Sold]),FILTER('Table',[Month]=FORMAT(Today(),"mmm")&&[Year]=Year(Today())-1&&Day([Order Date])<=Day(Today())))

Chihiro,
I am trying to calculate the YTD values of Last Year based on the Order Date. Example: 01/01/2017 thru 12/20/2017. I have attached updated sample data.

Thanks for your help

Chihiro · Dec 20, 2018

That's going to be bit more complex without Calendar dimension table.

However, you could use previously calculated Previous Year's MTD value ([PrevMTD]) to simplify steps.

Ex: Calculate Previous year's total up to previous month and add [PrevMTD]
PrevYTD:=CALCULATE(SUM([Units Sold]),FILTER('Table',[Year]=Year(TODAY())-1&&MONTH([Order Date])<MONTH(today())))+[PrevMTD]

sureshsonti · Dec 21, 2018

Chihiro said:
That's going to be bit more complex without Calendar dimension table.

However, you could use previously calculated Previous Year's MTD value ([PrevMTD]) to simplify steps.

Ex: Calculate Previous year's total up to previous month and add [PrevMTD]
PrevYTD:=CALCULATE(SUM([Units Sold]),FILTER('Table',[Year]=Year(TODAY())-1&&MONTH([Order Date])<MONTH(today())))+[PrevMTD]

Chihiro,
Awesome. You are a genius. This worked perfectly. I tried for this formula the whole day yesterday. Thanks for your help.

DAX (Comparison Today vs Last Year Same Day)

sureshsonti

Member

Attachments

Chihiro

Excel Ninja

sureshsonti

Member

sureshsonti

Member

Chihiro

Excel Ninja

sureshsonti

Member

Chihiro

Excel Ninja

sureshsonti

Member

sureshsonti

Member

Attachments

Nebu

Excel Ninja

Chihiro

Excel Ninja

sureshsonti

Member

sureshsonti

Member

Chihiro

Excel Ninja

sureshsonti

Member

sureshsonti

Member

Attachments

Chihiro

Excel Ninja

sureshsonti

Member