Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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.
Solved! Go to Solution.
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.
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. )
@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))
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.
Date | Training 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!
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
Thank you as well for the help. This is very close. You will see below your rolling average output (My RA) versus desired (7dayRA):
Your rolling average values mostly divide evenly into the desired values, strangely. One would think this observation would result in a speedy fix, though it appears I'm not yet savvy enough to figure out what's going on. Maybe you can figure it out?
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.
Thank you for your patience. I, too, hate retyping this data. The attached PBI file should satisfy your request.
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. )
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
83 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
110 | |
60 | |
57 |