powerbimoving-average

Calculate 30 day average in Power BI

I realize there are other threads on this topic, but none of them seem to work for me and I'm not sure why. I'm hoping someone might be able to shed some light on my issue.

I have the following raw data, where there are multiple accounts for each date:

Date Account Value
12/26/2023 AAA 497
12/26/2023 BBB 328
12/26/2023 CCC 398
12/22/2023 AAA 997
12/22/2023 BBB 709
12/22/2023 CCC 943
12/21/2023 AAA 825
12/21/2023 CCC 130
12/20/2023 AAA 275

What I'm looking for is something like the result column below, where the result is the average of the last three values in the 'Sum of Value' column inclusive of that day (e.g., 625 = (647+920+309)/3). Given the source of the data, the goal would be to sum each day first, then calculate an average over the last three days:

Date Daily Sum Result
20-Nov $309
21-Nov $920
22-Nov $647 $625
24-Nov $1,204 $924
27-Nov $1,010 $953
28-Nov $1,184 $1,132
29-Nov $1,014 $1,069
30-Nov $629 $942
1-Dec $1,491 $1,045
4-Dec $1,417 $1,179
5-Dec $1,150 $1,353
6-Dec $1,412 $1,327
7-Dec $1,129 $1,231
8-Dec $1,270 $1,271
11-Dec $586 $995
12-Dec $1,105 $987
13-Dec $702 $798
14-Dec $1,430 $1,079
15-Dec $1,169 $1,101
18-Dec $1,693 $1,431
19-Dec $753 $1,205
20-Dec $803 $1,083
21-Dec $955 $837
22-Dec $2,649 $1,469
26-Dec $1,224 $1,609

My problem is that no matter what formula I use, I'm only displaying the latest sum for a particular date ($1224 for 12/26, for example, instead of $1669). Can someone please help me understand how to do this properly given my data source?

Any help very much appreciated and happy holidays.

Thanks!

Solution

  • Try Measures similar to:

    Daily Sum = SUM(Example1[Value])

Avg Daily 3 = 
  var thisDate = MAX(Example1[Date])
  var last3 = 
    TOPN(3,
      SUMMARIZE(
        FILTER(ALL(Example1), Example1[Date] <= thisDate),
        Example1[Date],
        "Sum", [Daily Sum]
      ),
      [Date], DESC
    )
  return IF(COUNTROWS(last3) = 3, AVERAGEX(last3, [Sum]) )