Count Most Recent Streak

[donttakemyname]

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:

[Martin_D]

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] )
)

[donttakemyname]

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]

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.

[donttakemyname]

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: 

[Martin_D]

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.

[donttakemyname]

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

[Martin_D]

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?

[donttakemyname]

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 · Accepted solution]

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

[donttakemyname]

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.

[donttakemyname]

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_D]

Actually that's the result of the measure [Longest Streak]. Did you check my file?

[donttakemyname]

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