cancel
Showing results for
Search instead for
Did you mean: Frequent Visitor

## DAX Rolling 7 Day Average in User Selected Period

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.

1 ACCEPTED SOLUTION  Super User IV

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

3 REPLIES 3  Super User IV

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User! Frequent Visitor

@amitchandak
Sometimes I can't believe how hard you work, and how much you know looking through these forums. Thanks! Frequent Visitor

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. ## Helpful resources

Announcements #### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group! #### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks #### Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp! Top Solution Authors
Top Kudoed Authors
Users online (3,619)