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

How to restrict rolling average till last week

I have created a DAX for 7 day rolling average as -

 

OR Cases 7 Day Rolling Average =
AVERAGEX(
DATESINPERIOD( DimDate[Date], LASTDATE( DimDate[Date]), -7, DAY ), [OR Cases (Total)] )
 
How to restrict the date till last Saturday? The present day data should not be there. Ideally, I need 7 days rolling average (Sun-Sat) full week data. So if today is Monday (Dec 21), I want last 7 day rolling average till Dec 19.
1 ACCEPTED SOLUTION
Microsoft
Microsoft

Hi @monikamishra ,

 

Would you please add a weekday column in date table:

WEEKDAY = WEEKDAY(Dim_Date[Date],2)

Then try to use the following measure:

OR Cases 7 Day Rolling Average =
VAR A =
    CALCULATE (
        MAX ( 'Dim_Date'[Date] ),
        FILTER (
            ALL ( Dim_Date ),
            Dim_Date[Date] <= MAX ( Dim_Date[Date] )
                && Dim_Date[WEEKDAY] > 6
        )
    )
VAR NUM =
    MAX ( Dim_Date[Date] ) - A + 1
VAR TOTALVALUE =
    CALCULATE (
        [OR Cases (Total)],
        FILTER (
            ALL ( Dim_Date ),
            Dim_Date[Date] >= A
                && Dim_Date[Date] <= MAX ( Dim_Date[Date] )
        )
    )
RETURN
    TOTALVALUE / NUM

 

You can also refer to the sample pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/Ef5zxVgk6VBOqAeN5s...

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,

Dedmon Dai

 

 

View solution in original post

3 REPLIES 3
Microsoft
Microsoft

Hi @monikamishra ,

 

Would you please add a weekday column in date table:

WEEKDAY = WEEKDAY(Dim_Date[Date],2)

Then try to use the following measure:

OR Cases 7 Day Rolling Average =
VAR A =
    CALCULATE (
        MAX ( 'Dim_Date'[Date] ),
        FILTER (
            ALL ( Dim_Date ),
            Dim_Date[Date] <= MAX ( Dim_Date[Date] )
                && Dim_Date[WEEKDAY] > 6
        )
    )
VAR NUM =
    MAX ( Dim_Date[Date] ) - A + 1
VAR TOTALVALUE =
    CALCULATE (
        [OR Cases (Total)],
        FILTER (
            ALL ( Dim_Date ),
            Dim_Date[Date] >= A
                && Dim_Date[Date] <= MAX ( Dim_Date[Date] )
        )
    )
RETURN
    TOTALVALUE / NUM

 

You can also refer to the sample pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/Ef5zxVgk6VBOqAeN5s...

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,

Dedmon Dai

 

 

View solution in original post

Super User III
Super User III

Do you always want Sun-Sat? Then you'll need to use WeekNumber. Add WeekNumber to your DimDate table if you don't have it in there already. 

 

OR Cases Previous Week Average =
_MaxDate = MAX(DimDate[Date])
_weekNum = MAXX(FILTER(DimDate, DimDate[Date] = _MaxDate), DimDate[WeekNumber])
RETURN
AVERAGEX(
FILTER( DimDate, DimDate[WeekNum] = _weekNum-1 ), [OR Cases (Total)] )
 
This won't work over the new year, so you should really add some conditions if _weekNum = 1, then you'll need to use 
RETURN
AVERAGEX(
FILTER( DimDate, DimDate[WeekNum] = 53 ), [OR Cases (Total)] )
 
 

Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

Super User IV
Super User IV

@monikamishra , Think you need last Week , please check my blog

 

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

 

or try like example

//Last seven till week end 

last week =
Week Start date = maxx(allselected('Date'),'Date'[Date])+-7
Week End date = maxx(allselected('Date'),'Date'[Date])+-+ 7-1*WEEKDAY('Date'[Date],2) //sunday is week end 

 

return
CALCULATE(SUM(Sales[Net Sales]),FILTER(all('Date'),'Date'[Date]>=_min && 'Date'[Date]<=_max))

 

//use WEEKDAY('Date'[Date],1)  for saturday



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!

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors