in Power BI can I have one Sales Last Year Measure or must I have many

[Jay1999]

Very new to PowerBI and DAX so please bear with me.

I have a DIM_Date table with a financial calendar in (all dates no gaps) and a FACT_Sales table joined on date_id.

In Fact Sales I have a measure [Net_Sales] and was hoping to create one more column [Net_Sales_LY] to be used with any other related dimension aatribute i.e. Day, Week, Month, Year etc.

Looking on the internet for solutions and looks like I need to create a Net_Sales_LY_Day and then another Net_Sales_LY_Week, Net_Sales_LY_Month etc. but can I have only one LY measure?

Before I dive into any DAX I'm trying to get my head around the logic how best to do the LY i.e.

Do I add to my DIM_Date a running row_number and do a sum to deduct days, or have an alternate date on the date row and use this to look up a previous date. or use VAR Year=SelectYearNumber-1, Week=SelectWeekNumber, Day=SelectDayNumber

Any pointers would be appreciated and best ways, as I've been searching around last few hours but feel I dont fully understand what I should be looking for.

Thank you.

[Jay1999]

Thanks Alexis, the calculation groupings look very good, I'll dive in deeper.

[AlexisOlson · Accepted solution]

I'm not sure exactly what your question is but I'd recommend reading up on time intelligence functions that shift dates, like SAMEPERIODLASTYEAR, PARALLELPERIOD, and DATEADD.

This is also a good use case for calculation groups.

https://www.sqlbi.com/articles/introducing-calculation-groups/