Forum Discussion

Bi2thelly's avatar
Bi2thelly
Icon for Helper II rankHelper II
5 years ago

Count consecutive months in visual while filtering on Months

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 🙂
 

 

 

Need Help

2 Replies

  • Bi2thelly's avatar
    Bi2thelly
    Icon for Helper II rankHelper II
    5 years ago

    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.