Forum Discussion

donttakemyname's avatar
donttakemyname
Frequent Visitor
3 years ago
Solved

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:

 

 

 

  • Martin_D's avatar
    Martin_D
    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

     

14 Replies

  • Martin_D's avatar
    Martin_D
    Icon for Solution Sage rankSolution 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?

     

    • donttakemyname's avatar
      donttakemyname
      Frequent 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's avatar
        Martin_D
        Icon for Solution Sage rankSolution 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's avatar
    Martin_D
    Icon for Solution Sage rankSolution 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] )
    )

     

    • donttakemyname's avatar
      donttakemyname
      Frequent 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's avatar
        Martin_D
        Icon for Solution Sage rankSolution 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.