Streaks!
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
2 Replies
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 _MaxIndexHat Off, to both of you.
