Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
Im calculating the rolling 7 day sum of a field like so:
7 Day Rolling Sum ED Eval =
CALCULATE (
SUM ( 'Table'[Metric] ),
FILTER (
ALLSELECTED ( 'Table'[Date] ),
'Table'[Date]
<= MAX ( 'Table'[Metric] )
&& 'Table'[Date]
>= MAX ( 'Table'[Date] ) - 6
)
)
This gives me the rolling sum of metric like so
Date | Metric | Rolling 7 day Sum |
4/1/2020 | 1 | 1 |
4/2/2020 | 1 | 2 |
4/3/2020 | 1 | 3 |
4/4/2020 | 1 | 4 |
4/5/2020 | 1 | 5 |
4/6/2020 | 1 | 6 |
4/7/2020 | 1 | 7 |
4/8/2020 | 1 | 7 |
I would like another column like so:
Date | Metric | Rolling 7 day Sum | Rolling 7 day sum avg |
4/1/2020 | 1 | 1 | 1 |
4/2/2020 | 1 | 2 | 1.5 |
4/3/2020 | 1 | 3 | 2 |
4/4/2020 | 1 | 4 | 2.5 |
4/5/2020 | 1 | 5 | 3 |
4/6/2020 | 1 | 6 | 3.5 |
4/7/2020 | 1 | 7 | 4 |
4/8/2020 | 1 | 7 | 4.85 |
I cannot setup the rolling sum measure as a calculated column or something in the dataset as the rolling period needs to be dynamic.
Any help would be appreciated!
Solved! Go to Solution.
@hwr7dd , for a week, I usually add these in date tables
new columns
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format
measure like
Last 7 weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-7 && 'Date'[Week Rank]<=max('Date'[Week Rank])))
Hi, @hwr7dd
You could create a measure by the following:
avg =
AVERAGEX (
FILTER (
ALL ( 'Table' ),
[Date] <= MAX ( 'Table'[Date] )
&& [Date]
>= MAX ( 'Table'[Date] ) - 6),
[7 Day Rolling Sum ED Eval])
The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
In the third column of the second table, you want to create an average from the first of the month to the date of the current row. Is my understanding correct? Also, what is the 1 appearing at the end of the 2020? Furthermore, i do not see a Calendar Table. You must have a Calendar Table there.
@hwr7dd , Try a measure like this with help from the date table
Rolling 7 = CALCULATE(SUM ( 'Table'[Metric] ),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-7,DAY))
Beautiful clean DAX solution - pure as it was intended!
@amitchandak That gives me daily. Im trying to get Week over Week Rolling Average
@hwr7dd , for a week, I usually add these in date tables
new columns
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format
measure like
Last 7 weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-7 && 'Date'[Week Rank]<=max('Date'[Week Rank])))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |