Rolling month and end of month averages in calculated columns

[AmazingRandom]

I have been trying to aggregate daily volume data in to a rolling average and one end of month average, however, the source gives averages far higher than the daily volumes and any calculated columns/measures I have tried using are not working. For example:

Timestamp	Daily Volume Threshold	Daily Volume	Discharge Flow	MonthlyAvg
3/10/2024 0:00	15	0.006	0.02
2/10/2024 0:00	15	0.6499999934999999	0.573333328
1/10/2024 0:00	15	0	0
30/09/2024 0:00	15	0	0	0.319349997
29/09/2024 0:00	15	0.286999997	0.436666662
28/09/2024 0:00	15	0.020999999789999998	0.059999999400000004
27/09/2024 0:00	15	0.44499999554999997	0.606666661
26/09/2024 0:00	15	0	0
25/09/2024 0:00	15	0	0
24/09/2024 0:00	15	0.330999997	0.829999992
23/09/2024 0:00	15	0.105999999	0.216666665
22/09/2024 0:00	15	0.026999999729999998	0.083333333
21/09/2024 0:00	15	1.01299999	0.729999993
20/09/2024 0:00	15	0.287999997	0.463333329
19/09/2024 0:00	15	0.941999991	0.569999994
18/09/2024 0:00	15	0.17199999827999998	0.253333331
17/09/2024 0:00	15	0.20899999790999998	0.643333327
16/09/2024 0:00	15	0.5579999944200001	0.926666657
15/09/2024 0:00	15	0.503999995	0.876666658
14/09/2024 0:00	15	0.037999999619999995	0.126666665
13/09/2024 0:00	15	0.326999997	0.506666662
12/09/2024 0:00	15	0.16799999831999998	0.263333331
11/09/2024 0:00	15	0.005	0.016666667
10/09/2024 0:00	15	0.546999995	0.786666659
9/09/2024 0:00	15	0.062999999	0.106666666
8/09/2024 0:00	15	0	0
7/09/2024 0:00	15	0.814999992	0.796666659
6/09/2024 0:00	15	0.9539999904600001	0.689999993
5/09/2024 0:00	15	0.538999995	0.899999991
4/09/2024 0:00	15	0.191999998	0.639999994
3/09/2024 0:00	15	0.043	0.089999999
2/09/2024 0:00	15	2.296999977029999	1.033333323
1/09/2024 0:00	15	0.384999996	1.02999999

I ran this in excel so the average is supposed to be the 0.32 yet in the PBI calculated column it is saying 2.07 while in some other months it is even worse as it amount to the thousands when most daily values barely reach above 2. The calculated column is as follow, measure is pretty similar:

MonthlyAvg = 
IF(
    'Combined Trace Tables'[IsMonthEnd] = 1,
    CALCULATE(
        AVERAGE('Combined Trace Tables'[daily_volume]),
        FILTER(
            'Combined Trace Tables',
            YEAR('Combined Trace Tables'[Timestamp]) = YEAR(EARLIER('Combined Trace Tables'[Timestamp])) &&
            MONTH('Combined Trace Tables'[Timestamp]) = MONTH(EARLIER('Combined Trace Tables'[Timestamp]))
        )
    ),
    BLANK()
)

I am stryggling to edit and make this work, so I am open to any ideas.

[AllisonKennedy · Accepted solution]

@AmazingRandom 

This sounds like a context issue. If you're creating a Calculated Column to compute average, and then use that in a visual, Power BI will store one value for every row in the column, and then will aggregate those values in the visual, so you're essentially going to end up with a SUM of Averages, if that makes any sense?

Do you have a dimDate table? https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html

If so, you should be able to do a monthly average by using the Month of your date dimension, and AVERAGEX function ( https://www.dax.guide/averagex )

For example:

Monthly Average = 
AVERAGEX ( VALUES (dimDate[MonthYear] ), [Measure] )

From looking at your code, I think the [Measure] part of the expression above could be replaced with the SUM of your Daily Volume. NOTE: this will give you MONTHLY average, so yes, the numbers will be much higher than the daily volume, as it's adding them all up for the month.

If you just want a daily average for each month, then you can just use the below measure, and use your DimDate[MonthYear] column in the visual to group by Month:

Daily Average = 
AVERAGEX ( DimDate, [Measure] )

[AmazingRandom]

Thank you! Using the dimdate helped solve it