Forum Discussion

DannyDiaz's avatar
DannyDiaz
New Member
1 year ago
Solved

Need help - Get a table to identify consecutive values between groups

Hello, I have a table listing 3 types of products and hundreds of employees   SHOP    EMPLOYEE   PRODUCT   TIMELINE - DAY   A Tom Car 1 20 A Tom Car 1 21 ...
  • wardy912's avatar
    1 year ago

    Hi DannyDiaz 

     

     Add a calculated column to group streaks

     

    StreakGroup =
    VAR CurrentDay = 'ConsecutiveDays'[TIMELINE - DAY]
    VAR RankDay =
        RANKX (
            FILTER (
                'ConsecutiveDays',
                'ConsecutiveDays'[EMPLOYEE] = EARLIER ( 'ConsecutiveDays'[EMPLOYEE] )
                    && 'ConsecutiveDays'[PRODUCT] = EARLIER ( 'ConsecutiveDays'[PRODUCT] )
            ),
            'ConsecutiveDays'[TIMELINE - DAY],
            ,
            ASC
        )
    RETURN
    CurrentDay - RankDay

     

    This gives the following result, just adding column for visibility

     

     

    Now add a table that will summarize the streaks

     

    StreakSummary =
    SUMMARIZE (
        'ConsecutiveDays',
        'ConsecutiveDays'[EMPLOYEE],
        'ConsecutiveDays'[PRODUCT],
        'ConsecutiveDays'[StreakGroup],
        "ConsecutiveDays", COUNTROWS ( 'ConsecutiveDays' )
    )

    Next add a table that will filter the streaks as required

    QualifiedStreaks =
    FILTER (
        StreakSummary,
        [ConsecutiveDays] >= 7
    )

    Finally, add a table to show the result

    FinalResult =
    SELECTCOLUMNS (
        QualifiedStreaks,
        "EMPLOYEE", [EMPLOYEE],
        "PRODUCT", [PRODUCT],
        "CONSECUTIVE DAYS", [ConsecutiveDays]
    )

    Add this to a table visual for the following result:

    I hope this helps, please give a thumbs up and mark as solved if it does, thanks!