topic Re: Rolling average of a measure by day of week in DAX Commands and Tips

Rolling average of a measure by day of week

Oceanbagel — Mon, 06 Jun 2022 22:15:46 GMT

I have a measure that calculates the percent of table records meeting a threshold and I need to get the average of this measure for the last 4 occurrences of each weekday.

MEASURE = CALCULATE(COUNTROWS(ReportTable),ReportTable[Seconds] < 16) / COUNTROWS(ReportTable)

I have a rolling average of this measure that calculates the last 30 days:

30 DAY AVG =

VAR DateFilter =

DATESINPERIOD(

DateTable[Date],

MAX(DateTable[Date]),

-30,

DAY

)

VAR RollingSum =

CALCULATE(SUMX(ReportTable,[Measure])/COUNTROWS(ReportTable),DateFilter)

RETURN

RollingSum

My date table has day of week but it can't be used in DATESINPERIOD. I've tried a lot of different things but I'm stuck.

Any help would be greatly appreciated.

Re: Rolling average of a measure by day of week

amitchandak — Tue, 07 Jun 2022 03:36:19 GMT

Oceanbagel , You need to have measure like examples

Rolling 30 = calculate(AverageX(Values('Date'[Date]),[Measure]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-30,DAY))

Rolling 30 = calculate(AverageX(Values('Date'[Week Day]),[Measure]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-30,DAY))

Re: Rolling average of a measure by day of week

Oceanbagel — Tue, 07 Jun 2022 13:06:23 GMT

amitchandak , thanks for helping with this. Unfortunately this is still giving the average over 30 successive days. I'm trying to get the average for the last 4 Sundays, Mondays, etc.

Re: Rolling average of a measure by day of week

Oceanbagel — Tue, 07 Jun 2022 14:01:34 GMT

I was able to figure it out. Added a values filter to the calculate portion of the measure. It seems so obvious now.

Last 4 DoW =

VAR DateFilter =

DATESINPERIOD(

DateTable[Date],

MAX(DateTable[Date]),

-28,

DAY

)

VAR RollingSum =

CALCULATE(SUMX(ReportTable,[Measure])/COUNTROWS(ReportTable),DateFilter,VALUES(DateTable[DayofWeek]))

RETURN

RollingSum