Forum Discussion
Count Most Recent Streak
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
t doesn't need to go through multiple years, resetting at week 1 is fine. I'm making both a leader and loser board of the top and bottom 20 with this so I'm guessing calculating the longest streak wouldn't be an option, I'm still relativly new to power BI so I'm not quite sure
Solution SageI'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- Martin_D
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.
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