Calculate 30 day average in Power BI

Question

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!

Answer 1

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]) )

Answer 2

It's a pity that you have included images of your data rather than the actual figures so that we can cut, paste and test solutions.
There also seem to be typos in your question. If I'm correct, the sum for 26/12 should be $1,223. And the average of the 3 day's data 21,22 & 26 Dec should be 1609
Having said that, surely you are almost there. Is there any reason why you can't do this on with a two step process? You have the daily totals so calculating the average of 3 successive values is trivial - you already show it as "Result". If you don't want to display the intermediate daily values, just hide the "Sum of values" column.
The main problem seems to be irregular spacing of the dates and missing Accounts - there are only 2 sets for BBB whilst the rest are all AAA and CCC, except for 20 Nov which only has AAA. This makes it almost impossible to calculate your average in one step.