Count consecutive months in visual while filtering on Months

[Bi2thelly]

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 🙂

[Bi2thelly]

I am not able to provide the file because there is too much confidential data. I was able to create a workaround for the issue instead of counting months I would just compare the metric from the previous month and the current month.

[v-luwang-msft]

Hi @Bi2thelly ,

Could you pls share your pbix file? Remember to remove confident data.

Best Regards

Lucien