Calculating Y-O-Y difference up until a certain date

[Anonymous]

Hi,

I have a bar chart that breaks down sales by year. I then overlayed a line on it that shows Y-O-Y % difference in sales. It works for 2019 and 2020, but 2021 is way off because there has only been 1 month and it's comparing 1 month of sales to the entire year of 2020's sales. I've been suggested to make my date table end at 2/2/21, but that does not work in my context because I need future dates in my date table. Is there a way to make it so that 2021 sales data is compared to the respective months of 2020 rather than the entire year in DAX?

My current measure (which doesn't fit my needs):

Sales YoY% =

VAR __PREV_YEAR = CALCULATE(SUM('Example'[Sales]), DATEADD('Dates'[Date], -1, YEAR))

RETURN

    DIVIDE(SUM('Example'[Sales]) - __PREV_YEAR, __PREV_YEAR)

[VijayP]

@Anonymous 

Now use the Slicer to see the comparison at which level you want! Currenly you Axis is Year hence it is showing Yeear over Year and now you need to change the max period avialble in the Latest year in the Slicer to see the comparison

[Anonymous]

Thank you, I see now. Is there a way for the calculation to always be calculating from the max period available in this chart without the use of the slicer? I need the slicer to be selected for future dates for other charts in the dashboard.

[v-jingzhang · Accepted solution]

Hi @Anonymous 

Please try this measure. I assume that the last date in your sales data is the last sales date rather than the end date of a year. (It means you only have sales data until a certain date you mentioned)

Sales YoY% 2 = 
VAR _lastSalesDate = MAX(Example[Date])
VAR _year = YEAR(_lastSalesDate)
VAR _month = MONTH(_lastSalesDate)
VAR _day = DAY(_lastSalesDate)
VAR __PREV_YEAR = CALCULATE(SUM('Example'[Sales]), DATESBETWEEN('Date'[Date],DATE(_year-1,1,1),DATE(_year-1,_month,_day)))
RETURN
    DIVIDE(SUM('Example'[Sales]) - __PREV_YEAR, __PREV_YEAR)

Kindly let me know if this helps.
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.

[VijayP]

@Anonymous 

In Place of DATEADD Function to Calculate the Previous Year Number use SAMEPERIODLASTYEAR Function to get What  you Want!

[Anonymous]

Thanks for your response, I changed my measure to:

Sales YoY% =

VAR __PREV_YEAR = CALCULATE(SUM('Example'[Sales]), SAMEPERIODLASTYEAR('Dates'[Date]))

RETURN

    DIVIDE(SUM('Example'[Sales]) - __PREV_YEAR, __PREV_YEAR)

But it's still showing up as -97%. Did I do something wrong?