Forum Discussion
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 the top is the week number.
so the results would looksomething like:
2
14
1
0
etc.
this is the function that is displaying the ones:
and this the column that gives the week number:
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
14 Replies
- Martin_D
Solution Sage
Is this view always within one calendar year, or does the solution need to take into account that a streak can continue from week 52 to week 1 of the next year?
And would it be an option to calculate the longest streak per week and person at data refresh time in Power Query? Or are there other attributes that you need to filter interactively?
- donttakemynameFrequent Visitor
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
- Martin_D
Solution Sage
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
Looks like your code is counting all week numbers (including partial weeks at the beginning and end of year, according to how WEEKNUM() works). This indicates a missing relationship between your date table (the one that provides the week number column titles) and your fact table (the one that provides the Billable values).
If this is the case, you need to create a virtual relationship in your DAX code.
This code works in my file, without a week column, after reproducing your observation:Longest Streak (date based) = VAR _PersonsAndStreakLength = ADDCOLUMNS ( SUMMARIZECOLUMNS ( 'Name'[NameKey] ), "@StreakLength", // get the weeks and their WeekOver80 flag CALCULATE ( VAR _WeeksAndStreaks = CALCULATETABLE ( ADDCOLUMNS ( SUMMARIZECOLUMNS ( 'Date'[Date] ), "@WekOver80", VAR _Thursday = [Date] VAR _StartDate = _Thursday - 3 VAR _EndDate = _Thursday + 3 VAR _WeekDates = DATESBETWEEN ( 'Date'[Date], _StartDate, _EndDate ) RETURN CALCULATE ( COALESCE ( [WeekOver80], 0 ), TREATAS ( _WeekDates, 'Streak'[Date] ) ) ), WEEKDAY ( 'Date'[Date], 2 ) = 4 ) VAR _LastWeekWithoutStreak = MAXX ( FILTER ( _WeeksAndStreaks, [@WekOver80] = 0 ), [Date] ) VAR _StreakLength = COUNTROWS ( FILTER ( _WeeksAndStreaks, [Date] > _LastWeekWithoutStreak ) ) RETURN _StreakLength ) ) RETURN MAXX ( _PersonsAndStreakLength, [@StreakLength] )The relevant change is this part:
CALCULATE (
COALESCE ( [WeekOver80], 0 ),
TREATAS ( _WeekDates, 'Streak'[Date] )
)- donttakemynameFrequent Visitor
was there anything else you did to get it to work with your file? I tried it in both my own and the file you gave me and its showing up as blank in both
- Martin_D
Solution Sage
Yes, you need to adapt the measure and test it in your file. Obviously your dataset has different mechanics than my dataset - in my dataset the total aleady worked correctly, in yours not. The code with the TREATAS addresses this difference. If you want to try the measure in my file, you first need to corrupt it, so that it shows the same behavior as your file. You do this by disabling or deleting the relationship between the date and the fact table. Then the measure with TREATAS works, but the others no longer.
The implemantaion of a measure must always fit to the data model. That's why we are always asking for the measures and the datamodel. You cannot apply any code to any dataset, ignoring the relationships, and expect the same results.