cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Frequent Visitor

## Rolling average of a measure by day of week

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.
1 ACCEPTED SOLUTION
Frequent Visitor

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
3 REPLIES 3
Frequent Visitor

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
Super User

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

Frequent Visitor

@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.

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors