Forum Discussion
Count Most Recent Streak
- 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
thanks for the help Martin, my data is from a power bi data set so power query is a bit difficult to use, so I'll see what I can do to recreate the date table and keys.
But I was wondering if the name key is nessecary and if it is would something like this would work as it? All my users already have this type of unique identifier
For the users, any unique identifier does the job. For the week, it's important to have anything that sorts chronologically. If you have no week number in the dataset, then you can do the necessary steps in the measure (although this is not to the advantage of the performance): Where I group by WeekKey, you can instead group by the dates of all Thursdays (Thursday is always in the correct year when calculating week numbers) and if necessary, depending on how you implemented [WeekOver80], you need to extend the date context by +/- 3 days in the CALCULATE ( ... [WeekOver80] ... ) calculation. It's a bit more cumbersome without access to building the dataset, but it's doable.
- donttakemyname3 years agoFrequent Visitor
Everything has been working so far I just have one question, how did you get the total column on the right? Mine seems to be blank
- Martin_D3 years ago
Solution Sage
Actually that's the result of the measure [Longest Streak]. Did you check my file?
- donttakemyname3 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