Alberto Ferrari, you are a master, i have been trying to create custom period selected dates with a slicer to compare a selected year to a previous year. Your video sorted my problem quicksticks, FORZA FERRARI, i am staring at the results in the visual stunned and amazed. Thank You.😃😃
Is this replicatable for more than just PY? E.g. I've been trying to do running totals for 18 vs 19 vs 20 vs 21 (and now vs 22) - I only want to compare each against the current number of days in 22 - would I need a calculated column for each measure to ensure a true/false flag? I'm unsure whether SAMEPERIODLASTYEAR would support what I'm trying to do
Thank you for this little trick. I use to add Index columns in my date table, which calculates the "distance" between the actual date (This can be any date, e.g. the last date with Sales, or NOW() ), to each row in the date table by day, month, years, etc. This way I can solve a multitude of situations with little effort. Moreover, I add a column with the last date of the actual month for each date. This helps me in a lot of situation as well. I remove these columns if I don't need it.
Super, Great tip. Please suggest how to write a formula that will calculate and show in similar ways, but sales accumulated in the current year and in the column next to it in the previous year?
So, the issue I have with this particular approach is that you're putting a calculated column dependent on the fact table (a sales table) into the calendar table, e.g. pulling a summarized value from a "many" table into a "one" table. And I'm wondering if this would be a problem when slicing by, for example, product where different products might not have been sold on that day from the prior year (or may not have existed in the prior year). Question: Would it be better to create that calculated column in the Calendar table using logic exclusively from within the Calendar table by using the TODAY() function and referencing a prior period? For example, logic that looks at the date "today" which might be "day 200" and then looks back to the prior year for the corresponding "day 200". This presumes the presence of a sufficiently built-out Calendar dimension, although Power Query could be used for such a task. Regards!
This is a simple example of the technique. I suggest you look at the following patterns for a more complete coverage of the "fair comparison": www.daxpatterns.com/time-patterns/ --> more complete and flexible than this video www.daxpatterns.com/comparing-different-time-periods/ --> compare different ranges of dates www.daxpatterns.com/like-for-like-comparison/ --> solve the problem of products that were not on sale in the previous period
Great example! I've used % of year sell days complete this, but your example will be more preformant. There is an edge case where the 5th Sep this year is work day 5, while last year was Sep work day 3. This is why I went with % but your solution is very elegant
@@SQLBI thank you so much for the continued dedication of the development of BI. The naming conventions and calculation examples that link from this are truly fantastic
I have a similar problem. I want to show monthly data, only up to the last day. But biggest difference is that I have many variables, not just one (Sales in your example)...
I had done this but user wanted to have a date selection and make this dynamic, so couldn't use calc column method. Had done it with measure considering the selected date. Can you make a video on that? Thanks again. Enjoying DAX!!
The example in this video is simple and works for just one measure. A more flexible and complete technique (that requires more DAX) is described here: www.daxpatterns.com/standard-time-related-calculations/ However, performance is very good also with the more advanced technique.
what if you only want to calculate it for a specific area? We have sales coming in drips and drabs by area, how would you adjust this to only look at previous year sales against current year sales for a particular area?
Hi Sir, Thanks for this great trick, I was searching for a long time. One thing more I want to ask is that this will work for the current year & previous year fine but if we are comparing three or four years' values then how can we get data for such type mean at a particular date for a month in every year.
I see the same results by modifying the measure only. CALCULATE( [Sales Amount], SAMEPERIODLASTYEAR( 'Date'[Date]), 'Date'[Date] < EDATE(MAX('Sales'[Order Date])+1,-12) )
I'd be interested to see the result of this. When I try it, I get an error saying "A function 'MAX' has been used in a True/False expression that is used as a table filter expression. This is not allowed". Perhaps it has something to do with my choice of visual, but I need to use a matrix visual for my application, so if that's the reason, I'm out of luck. I appreciate you sharing your variation though. Definitely thought it would work for me.
In this case it is blank, but the behavior can be controlled in other ways (it also depends on you using DAX standard time intelligence functions or note). Look at the more complete coverage here: www.daxpatterns.com/time-patterns/
I am little bit surprised by the solution with categorizing previous year as partly "in past" and partly "in future". I see potential confusion in future usage of such a column. I usually solve this by using very similar column but containing a "real" distinction of past and future dates. In your example 9/6/2009 (and all following dates) would be future and 9/5/2009 would be the past. With that when I filter the column with only past dates everything works fine as well because that filters current year until today and hence the same period last year takes only corresponding part of past year too. Am I missing something here? What is the advantage of your approach comparing to one described above? I have to add that I am using that IsPast column to switch between the "adjusted" view and the "full" view of the whole previous year. Both views are used by our users.
this technique is simpler to use in Dax. a more complete and flexible approach that is similar to your description is the column ShowValueForFutureDates in www.daxpatterns.com/standard-time-related-calculations/
Hello Sir, Superb calculation.. Was having one doubt is there an performance issue using time intelligence functions.. As I have a requirement to calculate last year sales from current date so is it good idea to calculate the same in the table and then use it in the calculate for filtering purpose..
I tried this on dax and M-language. Needed to have this more dynamic so did it like this =if Date.Month(DateTime.LocalNow()) >= [MonthID] then if Date.Day(DateTime.LocalNow()) > [DayID] or Date.Month(DateTime.LocalNow()) > [MonthID] then true else false else false For me it worked... and same logic worked also on dax calc cols. With this it check backwards every year untills current date
Alberto Ferrari, you are a master, i have been trying to create custom period selected dates with a slicer to compare a selected year to a previous year. Your video sorted my problem quicksticks, FORZA FERRARI, i am staring at the results in the visual stunned and amazed. Thank You.😃😃
Thank you very much for this elegant solution!!!!!!!!
thanks so much
I have been searching for a whole week
I love the combination of a simple calculated column, and a simple DAX instead of complicated DAX only.
i have been looking for the solution for exact comparison for a while now.. this is simply brilliant. thanks Master Ferrari!
I was looking this solution for so long. Now I got that. Thanks a lot for making this vidio. Really helpful.
The man is a legend
Thank you Alberto for another short and sweet DAX Dose.
Thank you, I have been searching for this solution for 2 days now.
This is exactly what I was looking for. Thanks a lot for sharing
I wonder how would you do the same thing but with the cut-off date selectable in a slicer?
I'm stuck on the same issue, if you get a solution, pls let me know and I'll do the same for you
Is this replicatable for more than just PY?
E.g. I've been trying to do running totals for 18 vs 19 vs 20 vs 21 (and now vs 22) - I only want to compare each against the current number of days in 22 - would I need a calculated column for each measure to ensure a true/false flag? I'm unsure whether SAMEPERIODLASTYEAR would support what I'm trying to do
Thank you for this little trick.
I use to add Index columns in my date table, which calculates the "distance" between the actual date (This can be any date, e.g. the last date with Sales, or NOW() ), to each row in the date table by day, month, years, etc.
This way I can solve a multitude of situations with little effort.
Moreover, I add a column with the last date of the actual month for each date. This helps me in a lot of situation as well.
I remove these columns if I don't need it.
Super,
Great tip. Please suggest how to write a formula that will calculate and show in similar ways, but sales accumulated in the current year and in the column next to it in the previous year?
So, the issue I have with this particular approach is that you're putting a calculated column dependent on the fact table (a sales table) into the calendar table, e.g. pulling a summarized value from a "many" table into a "one" table. And I'm wondering if this would be a problem when slicing by, for example, product where different products might not have been sold on that day from the prior year (or may not have existed in the prior year). Question: Would it be better to create that calculated column in the Calendar table using logic exclusively from within the Calendar table by using the TODAY() function and referencing a prior period? For example, logic that looks at the date "today" which might be "day 200" and then looks back to the prior year for the corresponding "day 200". This presumes the presence of a sufficiently built-out Calendar dimension, although Power Query could be used for such a task. Regards!
This is a simple example of the technique. I suggest you look at the following patterns for a more complete coverage of the "fair comparison":
www.daxpatterns.com/time-patterns/ --> more complete and flexible than this video
www.daxpatterns.com/comparing-different-time-periods/ --> compare different ranges of dates
www.daxpatterns.com/like-for-like-comparison/ --> solve the problem of products that were not on sale in the previous period
oh man i needed this so hard!
Great example! I've used % of year sell days complete this, but your example will be more preformant. There is an edge case where the 5th Sep this year is work day 5, while last year was Sep work day 3. This is why I went with % but your solution is very elegant
You can find more complete (and more complex) examples of these comparisons in patterns at www.daxpatterns.com/time-patterns/
@@SQLBI thank you so much for the continued dedication of the development of BI. The naming conventions and calculation examples that link from this are truly fantastic
I have a similar problem. I want to show monthly data, only up to the last day. But biggest difference is that I have many variables, not just one (Sales in your example)...
I love this one! I am also going to take your DAX course soon !
highly worth it, I've purchase all of the DAX. he's definitely one of the better instructors!
Thank you so much Sir :)
Your Great 👍
hello, simple and clean! thank you!
Fantastic - really needed this!
Hi
I need to know why you used -12 in the end date function
And thanks 👍
I had done this but user wanted to have a date selection and make this dynamic, so couldn't use calc column method. Had done it with measure considering the selected date. Can you make a video on that?
Thanks again.
Enjoying DAX!!
The example in this video is simple and works for just one measure. A more flexible and complete technique (that requires more DAX) is described here: www.daxpatterns.com/standard-time-related-calculations/
However, performance is very good also with the more advanced technique.
@@SQLBI That is amazing. Thank you 👍
Thanks so much Alberto!
How to have same solution in Power Query @SQLBI
respect! thank you😊😊, i spent a lot of time to find this function🙏🙏🙏
How can this be used for multiple years?
what if you only want to calculate it for a specific area? We have sales coming in drips and drabs by area, how would you adjust this to only look at previous year sales against current year sales for a particular area?
It will be good if you can provide the dataset
You can find the link in the description!
but what if the last date of month is 30 April i should compare it with 31 of mar at this case as month closure
WOW, I love this! Thanks for sharing. 🙌
Can DateYTD ,MTD return correct result if we did not have sales evry day?
Yes, you just need a complete Date table.
Hi Sir,
Thanks for this great trick, I was searching for a long time. One thing more I want to ask is that this will work for the current year & previous year fine but if we are comparing three or four years' values then how can we get data for such type mean at a particular date for a month in every year.
Thanks. Another way is to use page filter with [Sales Amount] >0.
I see the same results by modifying the measure only.
CALCULATE(
[Sales Amount],
SAMEPERIODLASTYEAR( 'Date'[Date]),
'Date'[Date] < EDATE(MAX('Sales'[Order Date])+1,-12)
)
I'd be interested to see the result of this. When I try it, I get an error saying "A function 'MAX' has been used in a True/False expression that is used as a table filter expression. This is not allowed". Perhaps it has something to do with my choice of visual, but I need to use a matrix visual for my application, so if that's the reason, I'm out of luck. I appreciate you sharing your variation though. Definitely thought it would work for me.
Simply Great Sir.....
what will happen with the variable LastSaleDatePY when we have sales for 29th february ? would it return blank ?
In this case it is blank, but the behavior can be controlled in other ways (it also depends on you using DAX standard time intelligence functions or note). Look at the more complete coverage here: www.daxpatterns.com/time-patterns/
Great modification, however what is the impact on speed of calculation if the sales and date have 20 years of data?, thanks
There is almost no impact in performance.
@@SQLBI Thank you, I wish you a happy & healthy 2021 and many years to come!
I am little bit surprised by the solution with categorizing previous year as partly "in past" and partly "in future". I see potential confusion in future usage of such a column. I usually solve this by using very similar column but containing a "real" distinction of past and future dates. In your example 9/6/2009 (and all following dates) would be future and 9/5/2009 would be the past. With that when I filter the column with only past dates everything works fine as well because that filters current year until today and hence the same period last year takes only corresponding part of past year too. Am I missing something here? What is the advantage of your approach comparing to one described above?
I have to add that I am using that IsPast column to switch between the "adjusted" view and the "full" view of the whole previous year. Both views are used by our users.
this technique is simpler to use in Dax.
a more complete and flexible approach that is similar to your description is the column ShowValueForFutureDates in www.daxpatterns.com/standard-time-related-calculations/
Couldn't be done just with the measure? Add an extra argument on the calculate function
Yes, but the cost in performance would be relatively high, especially if you have many years in the Date table.
@@SQLBI cool 😎. Many thanks, keep up the good work!
shouldn't the equal "=" sign be removed from Date
Great stuff ! Thank you.
Its so great. Thank you so much ^^
What about, when I want to have a cumulative YTD?
You are the best
I was thingking use a filter. But its use DAX. Great way.
Normally, you want to have Date Type calculated in Power Query, as the best practice of data modeling. P/C/F
Hello Sir, Superb calculation.. Was having one doubt is there an performance issue using time intelligence functions.. As I have a requirement to calculate last year sales from current date so is it good idea to calculate the same in the table and then use it in the calculate for filtering purpose..
For more complex issue and more optimized code, look ad Time Intelligence patterns here: www.daxpatterns.com/time-patterns/
ha perfect solution to a lot of visuals in my report
I like it thank youuuu
I tried this on dax and M-language. Needed to have this more dynamic so did it like this
=if
Date.Month(DateTime.LocalNow()) >= [MonthID]
then
if
Date.Day(DateTime.LocalNow()) > [DayID]
or
Date.Month(DateTime.LocalNow()) > [MonthID]
then
true
else
false
else
false
For me it worked... and same logic worked also on dax calc cols. With this it check backwards every year untills current date
Merci beaucoup !!! C'est ce que je cherchais :) i'm saved