Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Help regarding YOY variance

Hi, I am trying to get the Year on Year variance using DAX.

I am new to DAX and have been searching for a solution however, I haven't be able to solve this problem. 

Below is the DAX I am using now, as you can see I am using date from a calendar table. and I am trying to get the yoy variance (instead of the YOY percentage I get in the query below) of the variable 'waterfall_TEMP All'[Rate_SalaryWages]'

I am wondering how I should change the DAX. Thank you very much for helping in advance

 

Rate_SalaryWages YoY% =
IF(
    ISFILTERED('calender'[Date]),
    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
    VAR __PREV_YEAR =
        CALCULATE(
            SUM('waterfall_TEMP All'[Rate_SalaryWages]),
            DATEADD('calender'[Date].[Date], -1, YEAR)
        )
    RETURN
        DIVIDE(
            SUM('waterfall_TEMP All'[Rate_SalaryWages]) - __PREV_YEAR,
            __PREV_YEAR
        )
)
  • amitchandak's avatar
    amitchandak
    5 years ago

    Anonymous , How that formula is different?

    YOY = divide([Current]-[Last], [Last])

    or

    YOY = divide([Current], [Last])

     

    example for YTD

    YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD("Date"[Date],"12/31"))
    Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd("Date"[Date],-1,Year),"12/31"))

     

    or

    This Year = CALCULATE(sum("order"[Qty]),filter(ALL("Date"),"Date"[Year]=max("Date"[Year])))
    Last Year = CALCULATE(sum("order"[Qty]),filter(ALL("Date"),"Date"[Year]=max("Date"[Year])-1))

     

    diff = [This Year]-[Last Year ]
    diff % = divide([This Year]-[Last Year ],[Last Year ])

     

    Make sure Calendar is marked as date table

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , How that formula is different?

    YOY = divide([Current]-[Last], [Last])

    or

    YOY = divide([Current], [Last])

     

    example for YTD

    YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD("Date"[Date],"12/31"))
    Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd("Date"[Date],-1,Year),"12/31"))

     

    or

    This Year = CALCULATE(sum("order"[Qty]),filter(ALL("Date"),"Date"[Year]=max("Date"[Year])))
    Last Year = CALCULATE(sum("order"[Qty]),filter(ALL("Date"),"Date"[Year]=max("Date"[Year])-1))

     

    diff = [This Year]-[Last Year ]
    diff % = divide([This Year]-[Last Year ],[Last Year ])

     

    Make sure Calendar is marked as date table

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    amitchandak Hi 
    I am only trying to get the YOY variance, 

    meaning getting the part where this year's YTD minus Last Year's YTD. 
    so I think it should be : diff = [This Year]-[Last Year ]

     

    I am trying to make sure I get the right data, the data of each year's YOY. 
    Do you have any suggestion on how I can check that?

    Thanks a lot