cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hwr7dd
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:

hwr7dd_0-1620574489239.png

This table shows with a max date of 1/07 and no min date (starting rolling period from minimum of data in the dataset).

hwr7dd_1-1620574560875.png

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:

DateRolling CensusCensus
1/03/2121992199
1/04/212246 ((2199+2293)/2)2293
1/05/212312((2199+2293+2445)/3)2445
1/06/212349((2199+2293+2445+2459)/4)2459
1/07/212375((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
amitchandak
Super User IV
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!

View solution in original post

3 REPLIES 3
amitchandak
Super User IV
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!

View solution in original post

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

hwr7dd
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
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors