Compare IDs for subsequent 3 months for each status and count

[rob_vander2]

Hi All, I have data with columns ID, Status and Month as attached screenshot. If I select particular Month, I want to compare IDs of that to next 3 subsequent month for each status so that I can create report as shown in attached screenshot. Let's say if I select Apr-25, I want to see movement from Apr-25 to May-25, I can see ID=1 moved from "High" to "Medium", similarlyI D=2 moved from "High" to "Medium", hence in May-25, "High" to "Medium" will be counted 2. Similarly we need to check all the movement. Let's I have 12 months in data, I want to see movement only upto 3 months

[v-echaithra]

Hi @rob_vander2 ,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,
Chaithra.

[v-echaithra]

Hi @rob_vander2 ,

We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Chaithra.

[johnt75]

You need 2 separate date tables, one for the slicer and 1 for the matrix. Mark the one you use for the matrix as a date table, and link both of them to the fact table.

You can then write a measure like

Num items = 
VAR CurrentDate = MAX( 'Date for slicer'[Year month] )
VAR SubsequentMonths = CALCULATETABLE(
	SUMMARIZE(
		'Table',
		'Date'[Date],
		'Table'[ID]
	),
	REMOVEFILTERS( 'Date for slicer' ),
	DATESBETWEEN( 'Date'[Date], EOMONTH( CurrentDate, 0) + 1, EOMONTH( CurrentDate, 4 ) )
)
	
VAR Result = CALCULATE(
	COUNTROWS( 'Table' ),
	KEEPFILTERS( SubsequentMonths ),
	REMOVEFILTERS( 'Date for slicer')
)
RETURN Result

[rob_vander2]

@burakkaragoz  I am not able to implement this. Could you please share PBIX file?

[burakkaragoz]

Hi @rob_vander2 ,

What you’re trying to achieve is a status “transition matrix” over rolling months for each ID, so you can track how statuses change for each ID across the next three months for any selected month.

Here’s a high-level approach you can use in Power BI with DAX:

1. Create a Self-Join Relationship in DAX:

   • You’ll need to create a calculated table or use DAX to reference the table against itself so you can compare the status of the same ID in consecutive months.
   • One approach is to use a DAX formula that joins the table to itself on [ID] and where the [Month] of the second table is exactly one, two, or three months after the [Month] of the first table.

2. Sample DAX for the Movement Table:
   Here’s a conceptual example:

   Status Movements =
   SELECTCOLUMNS (
       FILTER (
           CROSSJOIN (
               VALUES(Data[ID]),
               VALUES(Data[Month]),
               VALUES(Data[Status])
           ),
           // Only keep rows where the ID exists in current and next months
           Data[ID] = RELATED(Data[ID]) &&
           Data[Month] < RELATED(Data[Month]) &&
           Data[Month] >= RELATED(Data[Month]) &&
           Data[Month] <= DATEADD(RELATED(Data[Month]), 3, MONTH)
       ),
       "ID", Data[ID],
       "FromMonth", Data[Month],
       "FromStatus", Data[Status],
       "ToMonth", RELATED(Data[Month]),
       "ToStatus", RELATED(Data[Status])
   )

   (You’ll need to adapt this based on your actual table and relationships. The key idea is to match each [ID] and [Month] to the status in the next 1, 2, and 3 months.)

3. Build Your Matrix Visual:

   • Use the above table to create a matrix visual with [FromStatus] and [ToStatus] as rows and columns, filtering by the selected month.
   • Add a slicer for [Month] so you can select which starting month to analyze.

4. Count Transitions:

   • Use the COUNTROWS function in a measure to count the number of transitions from each status to another for each month.

If you need a more detailed DAX example tailored to your data model, let me know how your data table is structured or share the column names. Happy to help further!