Need help with writing DAX for specific problem

Hi Anonymous ,

 

Create two tables:

 

Calculation              ID

Price	1
Quantity	2
Revenue	3
Growth Ratio [Rev]	4

 

Time       ID

TY	1
PY	2
Delta	3
Delta %	4

 

Add the following measure:

Measure =
VAR CY =
    FILTER ( ALL ( 'Calendar'[Year] ); 'Calendar'[Year] = MAX ( 'Calendar'[Year] ) )
VAR PY =
    FILTER (
        ALL ( 'Calendar'[Year] );
        'Calendar'[Year]
            = MAX ( 'Calendar'[Year] ) - 1
    )
VAR Price_TY =
    CALCULATE ( [Price]; CY )
VAR Price_PY =
    CALCULATE ( [Price]; PY )
VAR Quantity_TY =
    CALCULATE ( [Quantity]; CY )
VAR Quantity_PY =
    CALCULATE ( [Quantity]; PY )
VAR Revenue_TY =
    CALCULATE ( [Revenue]; CY )
VAR Revenue_PY =
    CALCULATE ( [Revenue]; PY )
RETURN
    SWITCH (
        TRUE ();
        MAX ( 'Calculations'[Calculation] ) = "Price"
            && MAX ( TimeTable[Time] ) = "TY"; Price_TY;
        MAX ( 'Calculations'[Calculation] ) = "Price"
            && MAX ( TimeTable[Time] ) = "PY"; Price_PY;
        MAX ( 'Calculations'[Calculation] ) = "Price"
            && MAX ( TimeTable[Time] ) = "Delta"; Price_TY - Price_PY;
        MAX ( 'Calculations'[Calculation] ) = "Price"
            && MAX ( TimeTable[Time] ) = "Delta %"; FORMAT ( DIVIDE ( ( Price_TY - Price_PY ); Price_PY ); "#,00%" );
        MAX ( 'Calculations'[Calculation] ) = "Quantity"
            && MAX ( TimeTable[Time] ) = "TY"; Quantity_TY;
        MAX ( 'Calculations'[Calculation] ) = "Quantity"
            && MAX ( TimeTable[Time] ) = "PY"; Quantity_PY;
        MAX ( 'Calculations'[Calculation] ) = "Quantity"
            && MAX ( TimeTable[Time] ) = "Delta"; Quantity_TY - Quantity_PY;
        MAX ( 'Calculations'[Calculation] ) = "Quantity"
            && MAX ( TimeTable[Time] ) = "Delta %"; FORMAT ( DIVIDE ( ( Quantity_TY - Quantity_PY ); Quantity_PY ); "#,00%" );
        MAX ( 'Calculations'[Calculation] ) = "Revenue"
            && MAX ( TimeTable[Time] ) = "TY"; Revenue_TY;
        MAX ( 'Calculations'[Calculation] ) = "Revenue"
            && MAX ( TimeTable[Time] ) = "PY"; Revenue_PY;
        MAX ( 'Calculations'[Calculation] ) = "Revenue"
            && MAX ( TimeTable[Time] ) = "Delta"; Revenue_TY - Revenue_PY;
        MAX ( 'Calculations'[Calculation] ) = "Revenue"
            && MAX ( TimeTable[Time] ) = "Delta %"; FORMAT ( DIVIDE ( ( Revenue_TY - Revenue_PY ); Revenue_PY ); "#,00%" );
        BLANK ()
    )

 

Now make a matrix with the following setup:

• Rows: Calculation
• Columns: Lifecicle / Time
• Values: Measure

Drill down the matrix to the last level and add a slicer for year.

 

You need to add to the measure the calculation for revenue growth since I'm not sure how you want to calculate.

 

Check PBIX file with the result.

 

Regards,

MFelix