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 agoYou need to fix at least these issues (maybe more, depending on the relationships in your model):
- Your WeekKey does not sort chronological. It's text and the weeks are not always two digit. Think: 20181, 201810, ...20182, ... So as soon as you see a total, it can be wrong. You need numbers like 201801, 201802, ..., 201810, ...
- BillableForWeekKey2 takes the max date from the date table. Doublecheck with my file, it must come from the fact table. Look at your total column: Max week of date table is week 52 of your last year with data (Think: 52/2023 ?). In your data, no person might have billable over 80 for week 52/2023 yet. Thus no "1" to count for noone. Thus Total is blank. It works in the week columns, because they set the context of one week, then max date from date table is the last day of each week for each weeks calculation.
- Whether you need to apply the TREATAS approach or any other changes, depends on your data model.
donttakemyname
3 years agoFrequent Visitor
Alright thank you, I'm going to go ahead and mark your origional relpy as the solution as it obviously works, its just that my data is a hot mess that I don't even know if I will be able to fix. Thank you for both your patience, time, and skills