Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Running Average in a Matrix Visualization

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:

 

ClientWeekEndDateCharges
Client A4/4/2020123
Client B4/4/2020198
Client C4/4/2020164
Client A4/11/2020111
Client B4/11/2020164
Client C4/11/2020192
Client A4/18/2020133
Client B4/18/202094
Client C4/18/2020114
Client A4/25/2020160
Client B4/25/2020133
Client C4/25/2020141
Client A5/2/2020101
Client B5/2/2020124
Client C5/2/2020164
Client A5/9/2020138
Client B5/9/2020169
Client C5/9/2020111

 

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

 

 4/11/20204/18/20204/25/20205/2/20205/9/2020
Charges467341434389418
Rolling 14 Charges952808775823807
Rolling 14 Average Daily Chagres6857.755.458.857.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?

DAX Calculation
rolling dates
  • Anonymous's avatar
    Anonymous
    6 years ago

    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. 

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
    mahoneypat
    Microsoft Employee
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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.