Streaks!

Greg_Deckler — Fri, 09 Aug 2024 13:21:31 GMT

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

Re: Streaks!

AlexisOlson — Thu, 15 Aug 2024 16:46:42 GMT

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

Re: Streaks!

carlosbarboza — Sun, 06 Jul 2025 12:56:14 GMT

Hat Off, to both of you.

topic Streaks! in Quick Measures Gallery

Streaks!

Re: Streaks!

Re: Streaks!