Get count of consecutive months

[mpatel181]

I have a table with date (monh year) field and some data.  I have a measure that returns 0 and 1 for each month year.  I'm trying to see if there's a way to get count of consecutive months from the most current month where the measure value is 1.  Ex:  If curr month is Dec 2023 and my measure returns 1 for mos: Oct - Dec, then it should return 3.   There could be additioanl consecutive months (ex: Jan - May) with value of 1 but months in the middle have 0.  In this case, I still only care about Oct-Dec.  I've tried to do SUMX on my table but that didn't help.  Is there a loop function that I can use to iterate through the dates?

[Anonymous · Accepted solution]

Hi @mpatel181 

You can refer to the following measure.

Sample data 

I create the two measures to make the sample

Measure = SUM('Table'[Flag])

Measure 3 = DATE(2024,1,1)

(Use the measure 3 to change the date)

We need to  create a new measure

Measure2 =
VAR a =
    CALCULATE (
        [Measure],
        FILTER ( 'Table', EOMONTH ( 'Table'[Date], 0 ) = EOMONTH ( [Measure 3], 0 ) )
    )
VAR b =
    MAXX (
        FILTER (
            ALLSELECTED ( 'Table' ),
            [Measure] = 0
                && EOMONTH ( [Date], 0 ) <= EOMONTH ( [Measure 3], 0 )
        ),
        [Date]
    )
VAR c =
    MINX ( FILTER ( 'Table', [Date] > b ), [Date] )
RETURN
    IF ( a <> 0, DATEDIFF ( c, [Measure 3], MONTH ) + 1, 0 )

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mpatel181]

For some reason, when I add the EARLIER function with table[column], it's underline in red.  Any reason why?

[amustafa]

copy and paste your table and column name or the DAX you are testing with

[amustafa]

Here's a generic DAX measure DAX. 

ConsecutiveMonthsCount =
VAR CurrentMonth = MAX(YourTable[Date])
VAR FilteredTable =
FILTER(
YourTable,
YourTable[Date] >= CurrentMonth &&
YourTable[Measure] = 1
)
VAR CountConsecutiveMonths =
COUNTROWS(
ADDCOLUMNS(
FilteredTable,
"MonthDiff",
DATEDIFF(
EARLIER(YourTable[Date]),
YourTable[Date],
MONTH
)
)
)
RETURN
CountConsecutiveMonths