Forum Discussion
donttakemyname
3 years agoFrequent Visitor
Count Most Recent Streak
I've been looking at the similar questions here and I just cannot get anything to work. The ultimate goal of this is to make a leader board of who has the longest streak. left hand side is names and...
- 3 years ago
I'd recommend to create a date table with a week number, unique across all years, like 202252. Then the following code will give you the longest streak. The idea is:
- Check for each person
- what was the last week not included in a streak
- count the weeks after which all belong to the streak
- the longest streak is the maximum streak length for a single person
Longest Streak = // per person, calculate the latest week under 80, then counting the weeks after are the streak length VAR _PersonsAndStreakLength = ADDCOLUMNS ( SUMMARIZECOLUMNS ( 'Name'[NameKey] ), "@StreakLength", // get the weeks and their WeekOver80 flag VAR _WeeksAndStreaks = ADDCOLUMNS ( SUMMARIZECOLUMNS ( 'Date'[WeekKey] ), "@WekOver80", CALCULATE ( COALESCE ( [WeekOver80], 0 ) ) ) VAR _LastWeekWithoutStreak = MAXX ( FILTER ( _WeeksAndStreaks, [@WekOver80] = 0 ), [WeekKey] ) VAR _StreakLength = COUNTROWS ( FILTER ( _WeeksAndStreaks, [WeekKey] > _LastWeekWithoutStreak ) ) RETURN _StreakLength ) RETURN MAXX ( _PersonsAndStreakLength, [@StreakLength] )Check this file for details. Code to generate the date table in Power Query is included, if needed. The result looks like:
Longes streak top/flop 20
Martin_D
Solution Sage
3 years agoActually that's the result of the measure [Longest Streak]. Did you check my file?
donttakemyname
3 years agoFrequent Visitor
yeah I was looking at your file alot while making mine, super helpful btw. It looks to be an issue with the calculate portion of my longest streak function at the date context. I've tried doing the +/- 3 that you suggested but it doesnt calculate the actual total, it keeps giving me 53