Counting number of consecutive months

[buttercream]

Hi,

I'm trying to get a count of consecutive monthly purchases for each product.  What would be the best way to do this?

This is my table:

Product	Purchase Date
A001	11/1/2024
A001	10/8/2024
A001	8/31/2024
A200	12/15/2024
A009	10/21/2024
A009	12/10/2024

Need to make this visual:

Product	Consecutive Months
A001	2
A200	1
A009	1

[Ashish_Mathur]

Hi,

Why should the answer be 1 for A200 and A009?

[Anonymous · Accepted solution]

Hi @buttercream ,

I create a table as you mentioned.

Then I create a calculated column and here is the DAX code.

Month = MONTH('Table'[Purchase Date])

Next I create another calculated column.

IsConsecutive = 
VAR _CurrentProduct = 'Table'[Product]
VAR _CurrentMonth = 'Table'[Month]
VAR _PreviousMonth =
    CALCULATE (
        MAX ( 'Table'[Month] ),
        FILTER (
            'Table',
            'Table'[Product] = _CurrentProduct
                && 'Table'[Month] < _CurrentMonth
        )
    )
RETURN
    IF ( _CurrentMonth - _PreviousMonth = 1, 1, 0 )

Finally you can create a measure and get a new table.

Consecutive Months = SUM('Table'[IsConsecutive]) + 1

Best Regards

Yilong Zhou

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

[buttercream]

How do we account for year?  Dec 2024 to Jan 2025 should be counted as consecutive.

[ryan_mayu]

@Anonymous 

Hi Yilong, I went through your solution and want to learn a different solution. 

you used a tricky way that if monthnum is 1 that IsConsective will be 1 - blank , will still be 1.

However, I found a small bug in your solution

If there is a purchase date in Jan and there is not a purchase date in last year's Dec, then your IsConsecutive column will still display 1. 

Hope I didn't misunderstand your solution and very happy to learn a new way of thinking.

[ryan_mayu]

@buttercream 

you can try to create columns 

monthstart = EOMONTH('Table'[Purchase Date],-1)+1

Column = if( minx(FILTER('Table','Table'[Product]=EARLIER('Table'[Product])&&'Table'[monthstart]>EARLIER('Table'[monthstart])),'Table'[monthstart])=EDATE('Table'[monthstart],1),1,0)

then create a measure

Measure = sum('Table'[Column])+1

pls see the attachment below