Pivoting Calculated Columns or Alternative Solution

Anonymous Instead of using calculated columns, you can perform the necessary transformations in Power Query. This will allow you to unpivot the data and make it easier to visualize.

Unpivot the KPI columns so that you have a single column for KPI names and another for their values. This will make it easier to calculate the durations and visualize the data.

Calculate the durations for each KPI to return to green using DAX measures instead of calculated columns. This will allow you to create more flexible and dynamic visualizations.

Create a new table to store the durations for each KPI and ID.

DAX
Durations =
ADDCOLUMNS(
SUMMARIZE(
'Rolling Data',
'Rolling Data'[ID],
'Rolling Data'[Attribute]
),
"FirstROA", CALCULATE(
MIN('Rolling Data'[Date]),
FILTER(
'Rolling Data',
'Rolling Data'[ID] = EARLIER('Rolling Data'[ID]) &&
('Rolling Data'[Value] = "Red" || 'Rolling Data'[Value] = "Amber")
)
),
"NextGreenDate", VAR FirstROA = CALCULATE(
MIN('Rolling Data'[Date]),
FILTER(
'Rolling Data',
'Rolling Data'[ID] = EARLIER('Rolling Data'[ID]) &&
('Rolling Data'[Value] = "Red" || 'Rolling Data'[Value] = "Amber")
)
)
RETURN CALCULATE(
MIN('Rolling Data'[Date]),
FILTER(
'Rolling Data',
'Rolling Data'[ID] = EARLIER('Rolling Data'[ID]) &&
'Rolling Data'[Date] > FirstROA &&
'Rolling Data'[Value] = "Green"
)
),
"Duration", VAR FirstROADate = [FirstROA]
VAR NextGreenDate = [NextGreenDate]
RETURN IF(
NOT(ISBLANK(FirstROADate)) && NOT(ISBLANK(NextGreenDate)),
DIVIDE(
DATEDIFF(FirstROADate, NextGreenDate, DAY),
7,
0
),
BLANK()
)
)