Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Jay1999
New Member

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

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.

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

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/

View solution in original post

2 REPLIES 2
Jay1999
New Member

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

AlexisOlson
Super User
Super User

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/

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.