I'm running into an issue where I cannot figure out how to get consecutive months to increase in number based on a criteria in a visual. For instance in the screen shot below Mar 20 and Apr 20 are two consecutive months where a criteria was met. So the count should increase to 2. Same would go for Jun 20 and Jul 20.
My current measure is this:
Trigger CM OngoingLiveCosts =
Var OngoingCosts = [Ongoing Live Costs]
Var Rolling12 =
AVERAGEX(
DATESINPERIOD(
'Calendar'[Month_Yr],
MAX('Calendar'[Month_Yr]),
-12,
MONTH
),
[Ongoing Live Costs]
)
// Var CriteriaTable =
// FILTER(
// VALUES('Calendar'[Month_Yr]),
// OngoingCosts >= Rolling12*1.2
// && AND(
// 'Calendar'[Month_Yr] <= DATE(2020,4,1),
// 'Calendar'[Month_Yr] >= DATEADD('Calendar'[Month_Yr],-1,Month)
// )
// )
Return
CALCULATE(
COUNTROWS(
VALUES('Calendar'[Month_Yr])
),
FILTER(
VALUES('Calendar'[Month_Yr]),
OngoingCosts >= Rolling12*1.2
),
DATESINPERIOD(
'Calendar'[Month_Yr],
MAX('Calendar'[Month_Yr]),
-1,
MONTH
),
ALLEXCEPT('Calendar','Calendar'[Month_Yr])
)
But I have also used this.
CALCULATE(
COUNTROWS(
FILTER(
DATESINPERIOD(
'Calendar'[Month_Yr],
MAX('Calendar'[Month_Yr]),
-1,
MONTH
),
OngoingCosts >= Rolling12*1.2
)
),
REMOVEFILTERS('Calendar'[Month_Yr])
)I think I have been staring at this too long 🙂
