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

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

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:

The following is the result from Power BI :

Do test rigorously for all your test conditions.

Hope it helps!

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.