Previous year up to a certain date

[@Grundraak]

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.😃😃

[@Milhouse77BS]

I love the combination of a simple calculated column, and a simple DAX instead of complicated DAX only.

[@michaelmusora6942]

The man is a legend

[@excelisfun]

Thank you very much for this elegant solution!!!!!!!!

[@jameszhou162]

i have been looking for the solution for exact comparison for a while now.. this is simply brilliant. thanks Master Ferrari!

[@khardu]

Thank you Alberto for another short and sweet DAX Dose.

[@bhuprakashsharma6577]

I was looking this solution for so long. Now I got that. Thanks a lot for making this vidio. Really helpful.

[@stephencross4978]

Thank you, I have been searching for this solution for 2 days now.

[@mbianalytics]

This is exactly what I was looking for. Thanks a lot for sharing

[@HeliSal700]

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.

[@chenjoya7792]

oh man i needed this so hard!

[@MeirbhinOConaill]

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]

You can find more complete (and more complex) examples of these comparisons in patterns at www.daxpatterns.com/time-patterns/

[@MeirbhinOConaill]

@@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

[@cezaryczajka9198]

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?

[@lukaszk4388]

hello, simple and clean! thank you!

[@trevorbbeairsto]

Fantastic - really needed this!

[@jasonabbott3874]

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

[@maseratination]

I love this one! I am also going to take your DAX course soon !

[@dawnbrelsford8066]

highly worth it, I've purchase all of the DAX. he's definitely one of the better instructors!

[@rashenhouse]

respect! thank you😊😊, i spent a lot of time to find this function🙏🙏🙏

[@javiermancillavera3072]

Thanks so much Alberto!

[@1yyymmmddd]

I wonder how would you do the same thing but with the cut-off date selectable in a slicer?

[@stephencross4978]

I'm stuck on the same issue, if you get a solution, pls let me know and I'll do the same for you

[@jayshankarsingh7376]

Thank you so much Sir :)

[@ZiggyBoon]

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!

[@SQLBI]

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

[@Bharath_PBI]

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!!

[@SQLBI]

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.

[@Bharath_PBI]

@@SQLBI That is amazing. Thank you 👍

[@3danim8r1]

Simply Great Sir.....

[@jessmau]

WOW, I love this! Thanks for sharing. 🙌

[@KgasS]

Thanks. Another way is to use page filter with [Sales Amount] >0.

[@gayatrilshinde1560]

Your Great 👍

[@amirhd2080]

I was thingking use a filter. But its use DAX. Great way.

[@dataghostbi8619]

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.

[@MohamedGamal-pj6wd]

Hi I need to know why you used -12 in the end date function And thanks 👍

[@lamlya6908]

You are the best

[@uyenbui7036]

Its so great. Thank you so much ^^

[@akhildharimisetty2767]

It will be good if you can provide the dataset

[@SQLBI]

You can find the link in the description!

[@goldwhispers]

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?

[@AlanConfrey]

How can this be used for multiple years?

[@abdulrehman56]

How to have same solution in Power Query @SQLBI

[@1yyymmmddd]

Great stuff ! Thank you.

[@mikloshimics7789]

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) )

[@alexpetkunas9669]

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.

[@AliAlSayer]

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

[@sbn4862]

Can DateYTD ,MTD return correct result if we did not have sales evry day?

[@SQLBI]

Yes, you just need a complete Date table.

[@LuisRomaUSA]

ha perfect solution to a lot of visuals in my report

[@VladMZ]

Normally, you want to have Date Type calculated in Power Query, as the best practice of data modeling. P/C/F

[@IoriYagamiKOF98]

what will happen with the variable LastSaleDatePY when we have sales for 29th february ? would it return blank ?

[@SQLBI]

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/

[@pavlikjanata]

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.

[@SQLBI]

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/

[@NoShadowOfDoubt1]

Great modification, however what is the impact on speed of calculation if the sales and date have 20 years of data?, thanks

[@SQLBI]

There is almost no impact in performance.

[@NoShadowOfDoubt1]

@@SQLBI Thank you, I wish you a happy & healthy 2021 and many years to come!

[@juandavidbohorquezgiraldo3702]

I could do it in the same way but with the closing date of the last month for example if the current date is November 15 that compares me from September 30 of the current year with September 30 of the previous year

[@miranikkila9672]

What about, when I want to have a cumulative YTD?

[@mehdifadhli6413]

I like it thank youuuu

[@Halamahua]

Couldn't be done just with the measure? Add an extra argument on the calculate function

[@SQLBI]

Yes, but the cost in performance would be relatively high, especially if you have many years in the Date table.

[@Halamahua]

@@SQLBI cool 😎. Many thanks, keep up the good work!

[@siddheshamrutkar8684]

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..

[@SQLBI]

For more complex issue and more optimized code, look ad Time Intelligence patterns here: www.daxpatterns.com/time-patterns/

[@SamFisher-x2y]

shouldn't the equal "=" sign be removed from Date

[@jansjoblom1602]

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

[@alexandrab1534]

Merci beaucoup !!! C'est ce que je cherchais :) i'm saved