Forum Discussion
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 the prev and current quarter measure using switch.
| Category | Measure | Direction | prev | curr | diff | prev | curr | diff | prev | curr | diff | prev | curr | diff |
| Risk Index | ↑↓ | |||||||||||||
| Issues | # Open Issues | ↓ | 10 | 20 | 10 | 21 | 16 | -5 | 10 | 47 | 37 | 7 | 7 | 0 |
| >= M Issues | 2 | 3 | 1 | 3 | 2 | -1 | 2 | 7 | 5 | 1 | 0 | -1 | ||
| Average Resolution Time (days) | ↓ | 194 | 210 | 16 | 0 | 0 | 0 | 444 | 289 | -155 | 132 | 122 | -10 | |
| >= M Issues | 484 | 400 | -84 | 112 | 70 | -42 | 442 | 429 | -13 | 28 | 0 | -28 | ||
| Overdue Issues | ↓ | 3 | 3 | 0 | 4 | 3 | -1 | 1 | 8 | 7 | 0 | 0 | 0 | |
| >= M Issues | 0 | 0 | 0 | 1 | 0 | -1 | 1 | 3 | 2 | 0 | 0 | 0 | ||
| Incidents | # Open Incidents | ↓ | 10 | 20 | 10 | 21 | 16 | -5 | 254 | 47 | -207 | 7 | 7 | 0 |
| >= M Incidents | 2 | 3 | 1 | 3 | 2 | -1 | 444 | 7 | -437 | 1 | 0 | -1 | ||
| Average Resolution Time (days) | ↓ | 194 | 210 | 16 | 0 | 0 | 0 | 44 | 289 | 245 | 132 | 122 | -10 | |
| >= M Incidents | 484 | 400 | -84 | 112 | 70 | -42 | 347 | 429 | 82 | 28 | 0 | -28 | ||
| Overdue Issues | ↓ | 3 | 3 | 0 | 4 | 3 | -1 | 12 | 8 | -4 | 0 | 0 | 0 | |
| >= M Incidents | 0 | 0 | 0 | 1 | 0 | -1 | 0 | 3 | 3 | 0 | 0 | 0 |
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.
13 Replies
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.
Thanks Akash_Varuna for replying, I will try the above measure and would let you know.
Hi Akash_Varuna
We are calculating current measure like this :
VAR current__open= CALCULATE(SUMX(Issues,[Total Open Issues]), FILTER(Issues, QUARTER(Issues[Create Date]) = QUARTER(TODAY())))Hi Akash_Varuna
The current measure is calculated based on previous quarter -1 quarter and the previous quarter measure is just sumx(total open issues ), dateadd([max satatus date),-1, quarter). I also have a slicer which is based on created date Year/Quarter and Months.
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.
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
Hi reddevil ,
In additionto the Akash_Varuna , response. To enhance the accuracy and flexibility of your time based filtering, replace string filters with proper time intelligence functions like QUARTER(TODAY()) or DATEADD(). This ensures calculations dynamically adjust to the current date context instead of relying on static text-based filters.
Additionally, use VAR to store reusable values and maintain a dynamic date context that aligns with reporting periods. This improves clarity, performance, and consistency across different time-based metrics.
If your table contains multiple metric types, consider creating separate dynamic measures (Selected_OpenIssues, Selected_ResolutionTime) to accurately represent distinct reporting needs. This method provides greater flexibility in visualizing and analyzing various data categories within your report.
i Hope this helps...
Hi reddevil ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You.
Hi reddevil ,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
Thank you all for the help, Akash_Varuna the measure worked.
