Forum Discussion
mscopuser
2 years agoFrequent Visitor
Current Streak for Subject
Hi, I have this table (see below). From that table I want to have calculation of current streak for doing a sub project. I have tried chat gpt but no luck. Team member Client Projects...
- 2 years ago
mscopuser Sure, it's almost identical. Attached PBIX file below sig has the Current Streak measure as well as an example of Longest Streak for Project instead of sub-project.
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
NaveenGandhi
Memorable Member
2 years agoHI mscopuser
Please provide what is the expected output.
Regards,
NG
- mscopuser2 years agoFrequent Visitor
Updated. I just want to have number that I can use on a card visual. So I can see my current streak in studying serveral sub projects.