CODEWITHSUNDEEP
powerbidaxpowerquerypowerbi-desktop
Gradatc
Gradatc

Reputation: 125

DAX Formula - Calculate Rolling 5 Day average, always getting 5 days for average, but excluding weekends

I've tried a few ways of doing this, but can't seem to grok exactly how to get this done.

I have a large fact table full of charges with posting dates, and a calendar table associated via the posting date.

I want to get a rolling 5 day average of each day's total charges, but excluding the weekends, since those are exceedingly small.

So this, obviously, works for a rolling 5 day average including all days of the week. 'Date' is the Calendar Table CombinedData is the fact table [ChargesTotal] just calculates SUM(CombinedData[charges]).

AVERAGEX(DATESINPERIOD('Date'[Date], LASTDATE(CombinedData[postdate]), -5, DAY), [ChargesTotal])

I'm just not sure how to filter to always get 5 days, but not include weekends.

Any guidance is appreciated, and I am glad to clarify anything I presented poorly.

Upvotes: 0

Views: 836

Answers (1)

Chris
Chris

Reputation: 943

Try this (maybe you have to replace semicolon with comma),

in my Calendar-Table I have a Column DayOfWeek =WEEKDAY('Calendar'[Date];2)

Here are some measures, you are interessted in the last one:

Total Charges:=SUM(CombinedData[Charge])

Charges_Last_5_WeekDays:=CALCULATE([Total Charges]; DATESBETWEEN('Calendar'[Date]; LASTDATE('Calendar'[Date])-7;LASTDATE('Calendar'[Date])-1);'Calendar'[DayOfWeek]<=5)

AVG Charges:=AVERAGE(CombinedData[Charge])

AVG Charges_Last_5_WeekDays:=CALCULATE(AVERAGE('CombinedData'[Charge]); DATESBETWEEN('Calendar'[Date]; LASTDATE('Calendar'[Date])-7;LASTDATE('Calendar'[Date])-1))

enter image description here

Upvotes: 1

Related Questions