How to create table showing differences of two period/waves

[lsihui_]

Hi, 

In my current dataset, there are values for two waves in each year. The ideal output is to have a slicer to select any of the two waves and the matrix table will show the differences between the older wave and newer wave selected.

E.g.,

• if 2021 wave 1 and 2021 wave 2 is selected, the differences should be 200-100=100
• if 2024 wave 2 and 2023 wave 1 is selected, the differences should be 800-500=300

Please advise how I can set up this matrix table. Thank you!

Current dataset

[danextian · Accepted solution]

Hi @lsihui_ 

Create a separate dimension table for Year/Wave and then create this measure:

Difference = 
VAR FirstSelected =
    CALCULATE ( MIN ( YearWave[Year/Wave] ), ALLSELECTED ( YearWave ) )
VAR SecondSelected =
    CALCULATE ( MAX ( YearWave[Year/Wave] ), ALLSELECTED ( YearWave ) )
VAR FirstValue =
    CALCULATE (
        SUM ( Data[Value] ),
        KEEPFILTERS ( YearWave[Year/Wave] = FirstSelected )
    )
VAR SecondValue =
    CALCULATE (
        SUM ( Data[Value] ),
        KEEPFILTERS ( YearWave[Year/Wave] = SecondSelected )
    )
RETURN
    SecondValue - FirstValue

The measure will return the difference between max and min selected values. Selections in between these are ignored.

Please se the attached sample pbix.