Forum Discussion

lsihui_'s avatar
lsihui_
Frequent Visitor
1 year ago

How to create table showing differences of two waves

Hi, 

 

In my current dataset, there are some years with two waves of data and others just one wave of data. 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 wave 1, 2018 and wave 2, 2018 is selected, the differences should be 200-100=100
  • if 2024 and wave 1, 2019 is selected, the differences should be 900-300=600

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

 

Need Help

4 Replies

    • lsihui_'s avatar
      lsihui_
      Frequent Visitor
      1 year ago

      Thanks for the solution! Yes it helps. But I realized that the differences will be changed to positive if the later wave has a lower value than the earlier wave. E.g., if 2024 is 200 and 2023 is 300, the difference will show up as 100 instead of -100. Could you advise how I can fix this? 

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi lsihui_ ,

        Thanks for rajendraongole1's reply!
        And lsihui_ , This is my sample data:


        And you need another table with a column Index to identify the order of Wave so that DAX can keep the number with larger Index minus the number with smaller Index:


        Then use this DAX to create a measure:

        Value Difference = 
VAR SelectedWaves = VALUES('Wave Selection'[Wave Selection])
VAR Data =
    ADDCOLUMNS(
        SelectedWaves,
        "Index", LOOKUPVALUE('Wave Selection'[Index], 'Wave Selection'[Wave Selection], [Wave Selection]),
        "Value", LOOKUPVALUE('Table'[Value], 'Table'[Wave], [Wave Selection])
    )
VAR MaxIndex = MAXX(Data, [Index])
VAR MinIndex = MINX(Data, [Index])
VAR ValueMax = MAXX(FILTER(Data, [Index] = MaxIndex), [Value])
VAR ValueMin = MAXX(FILTER(Data, [Index] = MinIndex), [Value])
RETURN 
IF(
    COUNTROWS('Wave Selection') = 2,
    ValueMax - ValueMin,
    BLANK()
)

        And the final output is as below:


        Best Regards,
        Dino Tao
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.