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

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