Forum Discussion

reddevil's avatar
reddevil
Helper II
1 year ago
Solved

Table to be created using the below format

Hi Everyone: I have created a single measure for Prev and Current quarter and want to split it in a matrix using the below format, can someone guide me how do i achieve the below matrix table using ...
Need Help
  • Akash_Varuna's avatar
    Akash_Varuna
    1 year ago

    Hi reddevil Could you try this please create measures first for three seperatly and then do SWITCH statement in dax please check this 

    1. Create Measures:
      Define prev , curr , and diff measures:

      PrevMeasure = CALCULATE(SUM(Table[Value]), FILTER(Table, Table[Quarter] = "Previous"))  
CurrMeasure = CALCULATE(SUM(Table[Value]), FILTER(Table, Table[Quarter] = "Current"))  
DiffMeasure = [CurrMeasure] - [PrevMeasure]

    2. Dynamic Measure:

      SelectedMeasure = 
SWITCH(
    TRUE(),
    SELECTEDVALUE(Table[MeasureType]) = "Prev", [PrevMeasure],
    SELECTEDVALUE(Table[MeasureType]) = "Curr", [CurrMeasure],
    SELECTEDVALUE(Table[MeasureType]) = "Diff", [DiffMeasure],
    BLANK()
)

    3. Matrix Setup:

      Add Category and Measure to rows.

      Add MeasureType (Prev, Curr, Diff) to columns.

      Use SelectedMeasure as values.

    4. Apply conditional formatting for Direvtion as needed.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi reddevil,

The table you shared is a pivoted result table and it also not include any date fields. For these sample, we are hard to test and coding formula on it.

For previous date calculation, you can add a variable to get the current date. Then you can use current category and the current date to find the max date that less than current date and category equal to the current category as previouse date.

Regards,

Xiaoxin Sheng

reddevil's avatar
reddevil
Helper II
1 year ago

Thanks for your reply, but the above table is the one which i require as final output, the earlier measure provided by Akash is somewhat similar except we have a reporting period of every quarter end and previous quarter and using those date we have to calculate the measures like open issues, avg resolution time, can you please guide how do i calculate measures using reporting period of current quarter and previous quarters that would be helpful.