Forum Discussion

yakovlol's avatar
yakovlol
Resolver I
3 years ago
Solved

Count Consecutive Months for Users

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.

 

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.