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

     

grantsamborn's avatar
grantsamborn
Solution Sage
3 years ago

Let's start at the beginning.

 

Can you show the following?

 

1)  Please provide sample data that covers your issue or question completely. 

(I hate retyping sample data.)
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

 

2) Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

 

3) Please explain how you would expect to get from 1) to 2).

 

I would like to see a) your expected result AND b) how you intend to use it.

 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    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
    Solution Sage
    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. )