Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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

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)

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    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.

5 Replies

  • VijayP's avatar
    VijayP
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Anonymous 

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago
      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?
  • VijayP's avatar
    VijayP
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        v-jingzhang
        Icon for Community Support rankCommunity Support
        5 years ago

        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.