Streaks! 07-20-2024 08:43 AM - last edited 08-09-2024 06:21 AM

[Greg_Deckler]

To some extent, a practical implementation of Cthulhu (Cthulhu - Microsoft Fabric Community). However, these versions identify the longest streak and current streak, use dates instead of an index and is a much cleaner/better implementation of Cthulhu. 

A streak is simply something that has happened continuously, every day for a period of time.

Longest Streak Sub-Project = 
    VAR __SubProject = MAX( 'Table'[Sub-projects] )
    VAR __MinDate = 
        MINX( 
            FILTER( 
                ALL( 'Table' ), 
                [Sub-projects] = __SubProject 
            ), 
            [Start date] 
        )
    VAR __MaxDate = 
        MAXX( 
            FILTER( 
                ALL( 'Table' ), 
                [Sub-projects] = __SubProject 
            ), 
            [Start date] 
        )
    VAR __Table = 
        ADDCOLUMNS(
            CALENDAR( __MinDate, __MaxDate ),
            "__Count", 
                VAR __Date = [Date]
                VAR __Result = 
                    IF( 
                        COUNTROWS( 
                            FILTER( 
                                ALL( 'Table' ), 
                                [Start date] = __Date && [Sub-projects] = __SubProject 
                            ) 
                        ), 1, 0 
                    )
            RETURN
                __Result
        )
    VAR __Table1 = 
        ADDCOLUMNS(
            __Table,
            "Cthulhu", 
                VAR __Index = [Date]
                VAR __Group = [__Count]
                VAR __TmpTable1 = FILTER( __Table, [__Count] = __Group && [Date] < __Index)
                VAR __TmpTable2 = 
                    ADDCOLUMNS( 
                        __TmpTable1, 
                        "__diff", 
                        ( 
                            [Date] - 
                            MAXX( 
                                FILTER( 
                                    __Table, 
                                    [Date] < EARLIER( [Date] ) && [__Count] = EARLIER( [__Count] ) 
                                ), 
                                [Date] 
                            ) 
                        ) * 1. 
                    )
                VAR __Max = MAXX( __TmpTable2, [Date])
                VAR __MaxStart = MAXX( FILTER(__TmpTable2, [__diff] > 1), [Date])
                VAR __TmpTable3 = FILTER( __TmpTable2, [Date] >= __MaxStart)
                VAR __Result = 
                    SWITCH( TRUE(),
                        ISBLANK( __Max ), 1, 
                        __Max = __index - 1, COUNTROWS( __TmpTable3 ) + 1, 
                        1
                    )
            RETURN
                __Result
        )
    VAR __Result = MAXX( FILTER( __Table1, [__Count] <> 0 ), [Cthulhu] )
RETURN
    __Result

Current Streak Sub-Project = 
    VAR __SubProject = MAX( 'Table'[Sub-projects] )
    VAR __MaxDate = MAXX( ALL( 'Table' ), [Start date] )
    VAR __MinDate = 
        MINX( 
            FILTER( 
                ALL( 'Table' ), 
                [Sub-projects] = __SubProject 
            ), 
            [Start date] 
        )
    VAR __Table = 
        ADDCOLUMNS(
            CALENDAR( __MinDate, __MaxDate ),
            "__Count", 
                VAR __Date = [Date]
                VAR __Result = 
                    IF( 
                        COUNTROWS( 
                            FILTER( 
                                ALL( 'Table' ), 
                                [Start date] = __Date && [Sub-projects] = __SubProject 
                            ) 
                        ), 1, 0 
                    )
            RETURN
                __Result
        )
    VAR __Table1 = 
        ADDCOLUMNS(
            __Table,
            "Cthulhu", 
                VAR __Index = [Date]
                VAR __Group = [__Count]
                VAR __TmpTable1 = FILTER( __Table, [__Count] = __Group && [Date] < __Index)
                VAR __TmpTable2 = 
                    ADDCOLUMNS( 
                        __TmpTable1, 
                        "__diff", 
                        ( 
                            [Date] - 
                            MAXX( 
                                FILTER( 
                                    __Table, 
                                    [Date] < EARLIER( [Date] ) && [__Count] = EARLIER( [__Count] ) 
                                ), 
                                [Date] 
                            ) 
                        ) * 1. 
                    )
                VAR __Max = MAXX( __TmpTable2, [Date])
                VAR __MaxStart = MAXX( FILTER(__TmpTable2, [__diff] > 1), [Date])
                VAR __TmpTable3 = FILTER( __TmpTable2, [Date] >= __MaxStart)
                VAR __Result = 
                    SWITCH( TRUE(),
                        ISBLANK( __Max ), 1, 
                        __Max = __index - 1, COUNTROWS( __TmpTable3 ) + 1, 
                        1
                    )
            RETURN
                __Result
        )
    VAR __CurrentCount = MAXX( FILTER( __Table1, [Date] = __MaxDate ), [__Count] )
    VAR __Result = IF( __CurrentCount = 0, 0, MAXX( FILTER( __Table1, [Date] = __MaxDate ), [Cthulhu] ) )
RETURN
    __Result

eyJrIjoiZGVlMjU3ZGMtZjQ0NC00MjkwLWE3NjgtYWI1NzhjZWMzZWUwIiwidCI6Ijg3NDlmOWI5LWYzMmQtNDdhMS1hMjI0LTM2OTQxOGFlMmY1MSJ9

[AlexisOlson]

@Greg_Deckler, here's my simplification that should work at both the project and sub-project level:

Max Streak = 
VAR _MinDate = MIN ( 'Table'[Start date] )
VAR _MaxDate = MAX ( 'Table'[Start date] )
VAR _DateRange_ = CALENDAR ( _MinDate - 1, _MaxDate )
VAR _GapDates_ =
    ADDCOLUMNS (
        _DateRange_,
        "@IsGapDay", ISEMPTY ( FILTER ( 'Table', 'Table'[Start date] = [Date] ) )
    )
VAR _AddStreakIndex_ =
    ADDCOLUMNS (
        _GapDates_,
        "@StreakIndex",
            VAR _RowDate = [Date]
            VAR _LastGapDay =
                MAXX (
                    FILTER ( _GapDates_, [@IsGapDay] && [Date] <= _RowDate ),
                    [Date]
                )
            VAR _Index = INT ( _RowDate - _LastGapDay )
            RETURN
                _Index
    )
VAR _MaxIndex = MAXX ( _AddStreakIndex_, [@StreakIndex] )
RETURN
    _MaxIndex