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
id013
Helper V
Helper V

Comparing Volume over Multiple Years

Hi,

 

So I'm working on a visual that compares sales volumes from the current year and compares it to the same exact period last year. The way I wanted to do it is that the date slicer on the visual would only show the date range from the last year so 2/26/2020 to 2/26/2021. The user would select a date range  from the current year and the graph below would show the comparision between Current Year (CY) and Previous Year (PY).

 

id013_0-1614365599492.png

 

So I've created 2 sets of measures, one for the current year and another for the previous year both using the reference date of the current year. For example, if the user selects a date range of November 1 2020 to December 20 2020, it will show the sales volumes both for that date range as well as the sales volumes for the previous years equivalent, so Nov 1 2019 to December 20 2019. This works fine as I originally designed it, however, my users have requested to be able to see the groups separately so one showing just the current year and the other showing just the previous year. 
 
My problem is all my measures are done in reference to the current years date so for the previous years tab it still shows the current years dates. Current the data model is setup so there's only a single date dimension and I let the measures calculate the current year and previous year volume but the date of reference is always the current year, the previous year volume measures just make use of the SAMEPERIODLASTYEAR function.
 
I've seen other implementations that compare the same periods over multiple years however I've noticed that the x-axis seems to be common between the 2 periods, ie month, or fiscal period. Is it even possible to implement this solution where both periods are determined from the same date dimension?
 
Thanks
 
1 ACCEPTED SOLUTION
v-lionel-msft
Community Support
Community Support

Hi @id013 ,

 

v-lionel-msft_0-1617172724896.png

Or like this, compare the values of different years in the same month instead of being accurate to each day. In doing so you can create a month slicer.

Year Over Year (YOY) Comparisons in Power BI - YouTube

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
id013
Helper V
Helper V

Thanks for the help everyone 🙂 

v-lionel-msft
Community Support
Community Support

Hi @id013 ,

 

v-lionel-msft_0-1617172724896.png

Or like this, compare the values of different years in the same month instead of being accurate to each day. In doing so you can create a month slicer.

Year Over Year (YOY) Comparisons in Power BI - YouTube

 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

lbendlin
Super User
Super User

Absolutely.  You can create an additional calculated column that strips out the year part of your dates, and then use that column for your X axis. (Or you already use a proper calendar table which gives you that functionality for free) 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.