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
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.
oh okay sorry for all the trouble I'm still pretty new to power bi and data models. I manged to make a date table that is similar to what you had in your file using dax.
so thats what I got in terms of a date table. Your Billable calculate worked better then mine once I added your columns so im also using that.
and heres what I tried to do to fit your streak function into my data:
And in a matrix it looks like this:
You 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.
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