Count Consecutive Months for Users

[yakovlol]

Hello)

I have a problem could you please help me to count consecutive Months from the last available month in the data set for Users (column ID)

For example, for User 1 I want to have count 3 (3 months when a user with id 1 has values)

User 2 - the same 3
User 3 - 0 (because there is no values)
User 4 - 3 and so on

Maybe there are any chances to solve it?

Thank you so much for support.

[v-rzhou-msft · Accepted solution]

Hi @yakovlol ,

Firstly, please make sure your table looks like as below.

Or you can try UNPIVOT function to translate it in Power Query Editor.

Measure:

Measure = 
VAR _STEP1 =
    ADDCOLUMNS (
        'Table',
        "Flag",
            IF (
                EOMONTH ( 'Table'[Date], 0 )
                    + 1
                        IN CALCULATETABLE ( VALUES ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[ID] ) ),
                1,
                0
            ),
        "MaxDate",
            MAXX (
                FILTER ( 'Table', 'Table'[ID] = EARLIER ( 'Table'[ID] ) ),
                'Table'[Date]
            )
    )
VAR _STEP2 =
    ADDCOLUMNS (
        _STEP1,
        "PrevioiusDate",
            MAXX (
                FILTER ( _STEP1, [ID] = EARLIER ( [ID] ) && [Date] < [MaxDate] && [Flag] = 0 ),
                [Date]
            )
    )
RETURN
    COUNTAX (
        FILTER (
            _STEP2,
            [Date] > [PrevioiusDate]
                && [Date] <= [MaxDate]
                && [Value] <> 0
        ),
        [ID]
    )

Result is as below.

Best Regards,
Rico Zhou

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