Previous year up to a certain 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.😃😃

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

The man is a legend

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

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

Thank you Alberto for another short and sweet DAX Dose.

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

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

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.

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

oh man i needed this so hard!

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

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!

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

Your Great 👍

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

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

Thank you so much Sir :)

hello, simple and clean! thank you!

Fantastic - really needed this!

Thanks so much Alberto!

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

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.

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

Simply Great Sir.....

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

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?

WOW, I love this! Thanks for sharing. 🙌

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

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

It will be good if you can provide the dataset

How to have same solution in Power Query @SQLBI

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.

How can this be used for multiple years?

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

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

You are the best

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

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

Great stuff ! Thank you.

Its so great. Thank you so much ^^

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

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

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

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

I like it thank youuuu

ha perfect solution to a lot of visuals in my report

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

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