Previous year up to a certain date

This video describes how to compute previous year values up to a certain date in DAX and Power BI.
This technique is useful in case the data is presenting incomplete months or years.

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

[MilhouseBS]

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

[khardu]

Thank you Alberto for another short and sweet DAX Dose.

[jameszhou]

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

[bhuprakashsharma]

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

[stephencross]

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

[HeliSal]

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.

[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

[jessmau]

WOW, I love this! Thanks for sharing. 🙌

[rashenhouse]

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

[mikloshimics]

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

[cezaryczajka]

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?

[maseratination]

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

[jasonabbott]

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

[yyymmmddd]

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

[KgasS]

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

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

[juandavidbohorquezgiraldo]

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

[amirhd]

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