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

[DannyDiaz]

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
A	Tom	Car 1	22
A	Tom	Car 1	23
A	Tom	Car 1	24
A	Tom	Car 1	25
A	Tom	Car 1	26
A	Tom	Car 1	16
B	Tom	Car 2	22
B	Tom	Car 2	25

The expected result is a table to identify which employee had consecutive days equal or larger than 7 for each product, e.g:

EMPLOYEE	PRODUCT	CONSECUTIVE DAYS
Tom	Car 1	6

When the Timeline value was 16, it didn't had any consecutive value, same when value was 22 and 25 for Car 2.

Can you please help? Thanks 

[v-karpurapud]

Hi @DannyDiaz 

Thank you for posting your question on the Microsoft Fabric Community Forum, and thanks as well @Greg_Deckler , @alish_b and @wardy912  for the ongoing support and assistance.

Could you let us know if the suggested solution resolved your issue? This information can assist other community members facing similar challenges.

Thank you.

[wardy912 · Accepted solution]

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!

[DannyDiaz]

Thank you so much @wardy912, your solution worked for me!

[wardy912]

No problem, glad I could help!

[alish_b]

Hello @DannyDiaz ,

I replicated your data as follows: 

sampleTable = DATATABLE(
"SHOP", STRING,
"EMPLOYEE", STRING,
"PRODUCT", STRING,
"TIMELINE_DAY", INTEGER,
{
{"A", "Tom", "Car 1", 20},
{"A", "Tom", "Car 1", 21},
{"A", "Tom", "Car 1", 22},
{"A", "Tom", "Car 1", 23},
{"A", "Tom", "Car 1", 24},
{"A", "Tom", "Car 1", 25},
{"A", "Tom", "Car 1", 26},
{"A", "Tom", "Car 1", 16},
{"B", "Tom", "Car 2", 22},
{"B", "Tom", "Car 2", 25}
}
)

And then created the following measure as per your requirement (the basic logic behind it working is that it returns a value or the sum of consecutive days only if it matches or exceeds your threshold value that is 7, and returns a blank otherwise which is removed by default by Power BI)

Consecutive Days = 
VAR CurrentEmployee = SELECTEDVALUE(sampleTable[EMPLOYEE])
VAR CurrentProduct = SELECTEDVALUE(sampleTable[PRODUCT])
VAR threshold = 7

VAR TimelineDays = 
    CALCULATETABLE(
        VALUES(sampleTable[TIMELINE_DAY]),
        sampleTable[EMPLOYEE] = CurrentEmployee,
        sampleTable[PRODUCT] = CurrentProduct
    )

-- Convert to a table with row numbers for easier processing
VAR DaysWithIndex = 
    ADDCOLUMNS(
        TimelineDays,
        "RowNum", RANKX(TimelineDays, [TIMELINE_DAY], , ASC)
    )

-- Find consecutive sequences
VAR ConsecutiveGroups = 
    ADDCOLUMNS(
        DaysWithIndex,
        "GroupID", [TIMELINE_DAY] - [RowNum]
    )

-- Count max consecutive days in any group
VAR GroupCounts = 
    SUMMARIZE(
        ConsecutiveGroups,
        [GroupID]
    )

VAR MaxConsecutive = 
    MAXX(
        ADDCOLUMNS(
            GroupCounts,
            "ConsecutiveCount", 
            SUMX(
                FILTER(ConsecutiveGroups, [GroupID] = EARLIER([GroupID])),
                1
            )
        ),
        [ConsecutiveCount]
    )

-- Return result only if greater than or equal to threshold, otherwise blank
RETURN
    IF(
        NOT ISBLANK(CurrentEmployee) && 
        NOT ISBLANK(CurrentProduct) && 
        MaxConsecutive >= threshold,
        MaxConsecutive,
        BLANK()
    )

Please make changes to fields/tables as needed.

Also, I think you meant 7 as the total consecutive days in your result set (instead of 6 as 20-26 would be a total of 7 days and you also mentioned 7 as threshold) which means the following would be expected:

EMPLOYEE	PRODUCT	CONSECUTIVE DAYS
Tom	Car 1	7

The following is the result from Power BI :

Do test rigorously for all your test conditions.

Hope it helps!

[Greg_Deckler]

@DannyDiaz Not sure I 100% understand but it seems like you need something like Steaks. Streaks! - Microsoft Fabric Community