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,
I have data like so:
7 Day Rolling Census =
VAR sumper =
CALCULATE (
SUM ( 'Pace Alert Rollup'[Census] ),
DATESINPERIOD (
'Pace Alert Rollup'[Date],
MIN ( 'Pace Alert Rollup'[Date] ),
-7,
DAY
)
)
VAR daysinperiod =
CALCULATE (
DISTINCTCOUNT ( 'Pace Alert Rollup'[Date] ),
DATESINPERIOD (
'Pace Alert Rollup'[Date],
MAX ( 'Pace Alert Rollup'[Date] ),
-7,
DAY
)
)
RETURN
sumper / daysinperiod
I want the user to be able to select the period which the 7 day rolling average is running. For example:
This table shows with a max date of 1/07 and no min date (starting rolling period from minimum of data in the dataset).
This table filtered out the first couple days of the dataset, but still starts the rolling calculation as if there was no date filter. This table should read something like this:
Date | Rolling Census | Census |
1/03/21 | 2199 | 2199 |
1/04/21 | 2246 ((2199+2293)/2) | 2293 |
1/05/21 | 2312((2199+2293+2445)/3) | 2445 |
1/06/21 | 2349((2199+2293+2445+2459)/4) | 2459 |
1/07/21 | 2375((2199+2293+2445+2459+2483)/5) | 2483 |
So essentially Im wondering how to get that DATESINPERIOD function to take the dates set by a filter instead of those in the dataset.
Solved! Go to Solution.
@hwr7dd , I think you have take cumm approch with allselected . assuming date range is selected
Cumm Avg= divide( CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[date]))) ,
CALCULATE(distinctcount(Date[Month-Year]),filter(allselected(date),date[date] <=max(date[date])), not(isblank(Sales[Sales Amount]))))
or like
Cumm Avg= divide( CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[date]) && date[date] >=max(date[date])-7)) ,
CALCULATE(distinctcount(Date[Month-Year]),filter(allselected(date),date[date] <=max(date[date]) && date[date] >=max(date[date])-7 ), not(isblank(Sales[Sales Amount]))))
@hwr7dd , I think you have take cumm approch with allselected . assuming date range is selected
Cumm Avg= divide( CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[date]))) ,
CALCULATE(distinctcount(Date[Month-Year]),filter(allselected(date),date[date] <=max(date[date])), not(isblank(Sales[Sales Amount]))))
or like
Cumm Avg= divide( CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[date]) && date[date] >=max(date[date])-7)) ,
CALCULATE(distinctcount(Date[Month-Year]),filter(allselected(date),date[date] <=max(date[date]) && date[date] >=max(date[date])-7 ), not(isblank(Sales[Sales Amount]))))
@amitchandak
Sometimes I can't believe how hard you work, and how much you know looking through these forums. Thanks!
Also, quick note for anyone who may find this in the future, I had to change -7 days to -6 days to line up with my correct rolling 7 day avg.
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 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |