How to restrict rolling average till last week

[Anonymous]

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.

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ,

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/Ef5zxVgk6VBOqAeN5s3vLkoBvLMH0XaozwCgGMEJyj1QBQ?e=bmgzTh

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

[AllisonKennedy]

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

similar to in this blog: https://forum.enterprisedna.co/t/previous-weeks-calculation/3559

[amitchandak]

@Anonymous , 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-Last-Week/ba-p/1051123

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