Running Average in a Matrix Visualization

[Anonymous]

I've looked at several rolling average posts, but none of them seem to fit my situation and data. Let's say I have this in a table:

Client	WeekEndDate	Charges
Client A	4/4/2020	123
Client B	4/4/2020	198
Client C	4/4/2020	164
Client A	4/11/2020	111
Client B	4/11/2020	164
Client C	4/11/2020	192
Client A	4/18/2020	133
Client B	4/18/2020	94
Client C	4/18/2020	114
Client A	4/25/2020	160
Client B	4/25/2020	133
Client C	4/25/2020	141
Client A	5/2/2020	101
Client B	5/2/2020	124
Client C	5/2/2020	164
Client A	5/9/2020	138
Client B	5/9/2020	169
Client C	5/9/2020	111

What I'm ultimately looking for is a rolling average I can display in a matrix. Desired outcome:

	4/11/2020	4/18/2020	4/25/2020	5/2/2020	5/9/2020
Charges	467	341	434	389	418
Rolling 14 Charges	952	808	775	823	807
Rolling 14 Average Daily Chagres	68	57.7	55.4	58.8	57.6

I started here, which feels close:

AverageCharges14Days =
calculate(
sum('Table1'[Charges]),
filter (
all ( 'Table1'[WeekEndDate] ),
'Table1'[WeekEndDate] <= max('Table1'[WeekEndDate])
&& 'Table1'[WeekEndDate] > ( max('Table1'[WeekEndDate])-14)
)
)/14

That method works if I want to do a 7 day average, since each column header is 7 days. When I make it 14 days, though, it just gives me half the 7 day value. I can tell it's simply not including the previous week's Charges in the sum() function, so I guess the filter(all()) isn't what I need. Can someone help me get to the desired outcome?

[Anonymous · Accepted solution]

I have solved it. Looks like instead of filter(all()) I should have been using DATESINPERIOD. Thus:

AverageCharges14Days =
calculate(
sum('Table1'[Charges]),
DATESINPERIOD('Table1'[WeekEndDate],LASTDATE('Table1'[WeekEndDate]),14,DAY)
)/14

This gave me the output I was looking for. 

[mahoneypat]

I think you just need >= instead of > to compare to the -14 date.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Unfortunately, that didn't do anything. I'm pretty sure it's because PBI is only running the measure on lines within that weekendingdate column value. I don't know how to get it to add the previous week's charges before dividing.