Forum Discussion
Table to be created using the below format
- 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
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]
Dynamic Measure:
SelectedMeasure = SWITCH( TRUE(), SELECTEDVALUE(Table[MeasureType]) = "Prev", [PrevMeasure], SELECTEDVALUE(Table[MeasureType]) = "Curr", [CurrMeasure], SELECTEDVALUE(Table[MeasureType]) = "Diff", [DiffMeasure], BLANK() )Matrix Setup:
Add Category and Measure to rows.
Add MeasureType (Prev, Curr, Diff) to columns.
Use SelectedMeasure as values.
Apply conditional formatting for Direvtion as needed.
Hi reddevil ,
Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
Hi XiaoXin,
I have already provided a table above which has measures in rows and there is previous and current quarters(columns) which needs to be calculated against those measures. There is also a date slicer on top. Please let me know if you require any further information.
- Anonymous1 year agoNot applicable
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
- reddevil1 year agoHelper II
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.