Greg_Deckler
Community Champion
2 years agoStreaks!
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...
AlexisOlson
Super User
2 years agoGreg_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