Forum Discussion

big_ozzie1's avatar
big_ozzie1
Frequent Visitor
3 years ago
Solved

Max of rolling average expression

I have a measure that returns the 7-day rolling average for the Training Load metric. It is working just fine:

 

 

7dayRA = 
VAR numdays = 7
VAR _sum = CALCULATE(SUM('RPE/Wellness'[Training Load]),DATESINPERIOD('RPE/Wellness'[date], LASTDATE('RPE/Wellness'[date]),-numdays, DAY))
RETURN _sum / numdays

 

 

I want to find the max of the rolling average result set. You see below it is 197.14.

I have tried:

 

Test1 = CALCULATE(MAXX(VALUES('RPE/Wellness'), [7dayRA]))

 

and

 

Test2 = CALCULATE(MAXX('RPE/Wellness', [7dayRA]))

 

 

You see their results in the table. These are the solutions I have found in forums, but neither returns the result 197.14 I am looking for.

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    3 years ago

    Hi,

    These measures work

    TL = SUM('RPE/Wellness'[Training Load])
    TL in previous week = CALCULATE([TL],DATESBETWEEN('Date'[Date],MIN('Date'[Date])-6,min('Date'[Date])))
    7 day rolling average = if(ISBLANK([TL]),BLANK(),DIVIDE([TL in previous week],7))
    All time high rolling average = if(ISBLANK([TL]),BLANK(),MAXX(ALL('Date'),[7 day rolling average]))

    Also, to the visul, drag Date from the Date Table.

    Hope this helps.

  • grantsamborn's avatar
    grantsamborn
    3 years ago

    Hi big_ozzie1 

     

    This time I totalled the Training Load for the last 7 days and divided it by 7.  (It appears to be what you were doing with your [7dayRA] only using a date table to account for missing dates.)

     

    My RA = 
VAR _CurrDt = SELECTEDVALUE( 'Date'[Date] )
VAR _LastWk = _CurrDt - 7
VAR _SumOfLastWk =
    CALCULATE(
        [Total],
        FILTER(
            ALL( 'Date'[Date] ),
            'Date'[Date] > _LastWk
                && 'Date'[Date] <= _CurrDt
        )
    )
RETURN
    _SumOfLastWk / 7

     

     

    (Note: Looking at rolling totals sorted descendingly seems to be counter-intuitive for me.  If it was me, I'd only look at the dates in a descending order AFTER I was convinced they worked in ascending order.  But that's just my opinion.)

     

    pbix: Help - mine.pbix

     

    Let me know how this goes.

     

    (Also, my apologies to Ashish_Mathur  since I had came up with my solution last night I didn't see your almost identical solution. )

     

8 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    big_ozzie1 , You should always use date table in such cases. Try like

     

    7dayRA =
    VAR numdays = 7
    return CALCULATE(AVERAGEX(Values(Date[Date]), calculate(SUM('RPE/Wellness'[Training Load]))),DATESINPERIOD('Date'[date], Max('Date'[date]),-numdays, DAY))

    • big_ozzie1's avatar
      big_ozzie1
      Frequent Visitor
      3 years ago

      Thanks very much for the response. This works with the MAXX function, but unfortunately your rolling average calculation does not take null days into account. The application here is sports, so I need to account for days with no data as well.

      For example, for this table, the 7-day rolling average should be 111.43. With yours it is 260.

      DateTraining Load
      8/8/2022  300
      8/7/2022 
      8/6/2022  240
      8/5/2022 
      8/4/2022  240
      8/3/2022 
      8/2/2022 

      Is there any way around the blank data issue? Thanks!

      • grantsamborn's avatar
        grantsamborn
        Solution Sage
        3 years ago

        Hi big_ozzie1 

        Would something like this help?

         

        Total = SUM( 'RPE/Wellness'[Training Load] )

My RA = 
VAR _Days = 7
VAR _CurrDt = SELECTEDVALUE( 'Date'[Date] )
VAR _LastWk = _CurrDt - _Days
VAR _Result =
    AVERAGEX(
        FILTER(
            ALL( 'Date'[Date] ),
            'Date'[Date] >= _LastWk
                && 'Date'[Date] <= _CurrDt
        ),
        [Total]
    )
RETURN
    _Result / _Days


Max of RA = 
    MAXX(
        ALL( 'Date'[Date] ),
        [My RA]
    )

         

         

        In this pbix, I looked at 2 different methods.  (I originally thought that you weren't worried about the dates with no values.)

         

        pbix: MAX of 7-day Rolling Average.pbix