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:   Client WeekEndDate Charges Client A 4/4/2020 123 C...
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. 

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.