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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors