Forum Discussion

id013's avatar
id013
Helper V
5 years ago
Solved

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

 

 

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
 
Need Help

3 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

    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) 

  • v-lionel-msft's avatar
    v-lionel-msft
    Community Support
    5 years ago

    Hi id013 ,

     

    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.