Table to be created using the below format

[reddevil]

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

[reddevil]

 Thank you all for the help, @Akash_Varuna the measure worked.

[V-yubandi-msft]

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.

[V-yubandi-msft]

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.

[V-yubandi-msft]

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...

[Anonymous]

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

[reddevil]

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.

[Anonymous]

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]

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.

[Akash_Varuna · Accepted solution]

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.

[reddevil]

Hi 

Can you please help.

[reddevil]

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.

[reddevil]

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())))

[reddevil]

Thanks @Akash_Varuna for replying, I will try the above measure and would let you know.