Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

% Variation Over Years - DAX synthax issues

Hi the community, 

 

I'm making progress with PBI, specially in YOY analysis, but i'm still stuck with beginner issues... 

 

So here is my problem : 

 

I was looking to calculate variation quantity YOY, like in this example : 

 

Year MonthOrder QuantityQuantity LYQuantity Diff between CY LY
Apr 194421026-584
May 201982373093

 

I created a calandar table to help me in YOY comparaison, like it seems to be recommended. 

 

I did those two calculation for the while : 

 

- Quantity LY = CALCULATE(SUM("Sheet 1" ^[Order Quantity]), SAMEPERIODLASTYEAR ("Calendar Table" [Date])

- Quantity Diff Between CY LY = SUM("sheet 1" [Order Quantity] - [Quantity LY]

 

Now i'm trying go get this Difference in Variation %  : 

 

I tried this : 

% Variation CY LY =
VAR _currentyear = SUM('Scrap Projection'[Order Quantity])
VAR _LastYear = [Quantity LY]
RETURN
_currentyear - _LastYear / _currentyear

 

But my result had no sense... 

I'm sure i'm missing something simple... Anyone can advise me ? 

 

Thanks in advance 🙂 

  • Pretty sure you want:

     

    % Variation CY LY =
    VAR _currentyear = SUM('Scrap Projection'[Order Quantity])
    VAR _LastYear = [Quantity LY]
    RETURN
    (_currentyear - _LastYear) / _currentyear

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion

    Pretty sure you want:

     

    % Variation CY LY =
    VAR _currentyear = SUM('Scrap Projection'[Order Quantity])
    VAR _LastYear = [Quantity LY]
    RETURN
    (_currentyear - _LastYear) / _currentyear
    • Anonymous's avatar
      Anonymous
      Not applicable

      thank you so much Greg for your help 🙂 

      Wish you the best !