Need help with writing DAX for specific problem

[Anonymous]

Hi all,

How could I achieve below requirement.

PY --> Previous Year (based on year selection in slicer)
TY --> This Year  (based on year selection in slicer)

Below you can find the pbix file.

https://drive.google.com/open?id=1kCtpjmQubCnOsGSU2Kf1PyLej5GrtnDq

Lifecycle

Introduction

.

.

.

Growth

.

.

.

Maturity

.

.

.

PY

TY

Delta

Delta %

PY

TY

Delta

Delta %

PY

TY

Delta

Delta %

QTYS

Price

Revenue

Growth Ratio [Rev.]

Thanks,
AS

[MFelix · Accepted solution]

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

[Anonymous]

Hey @MFelix ,

Thanks That's what I was looking for. I also started in the same way but couldn't figure out how to use them all in one measure. Revenue growth is the same ratio of this year to last year. But it's already covered with Delta%.

Again, thanks a ton for the quick help.

Have a nice Christmas time and happy holidays.

[Greg_Deckler]

See if my Time Intelligence the Hard Way provides a method of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...