Cumulative Average but for a specific date range

Hi SeanGaffney 

Please try this one:

OptimizedCumulativeAverage = 
VAR SelectedDate = MAX('Calendar'[Date])
VAR YearMonthValue = SELECTEDVALUE('Calendar'[YearMonth]) // Create yyyymm column
RETURN
CALCULATE(
    AVERAGE('Roast'[Spec 430nm Colour]),
    FILTER(
        ALLSELECTED('Roast'),
        'Roast'[YearMonth] <= YearMonthValue
    )
)

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.

Hi SeanGaffney,

Thanks for reaching out to the Microsoft fabric community forum.

To enable a rolling average by month in Power BI, we first create a Calendar table using DAX, including both YearMonth and YearMonthNumber for sorting:

Here’s the DAX measure I used:

Calendar =
VAR MinDate = CALCULATE(MIN('Roast'[Date]), ALL('Roast'))
VAR MaxDate = CALCULATE(MAX('Roast'[Date]), ALL('Roast'))
RETURN
ADDCOLUMNS(
CALENDAR(MinDate, MaxDate),
"YearMonth", FORMAT([Date], "YYYY-MM"),
"YearMonthNumber", YEAR([Date]) * 100 + MONTH([Date])
)

We relate Calendar[Date] to Roast[Date], and sort YearMonth by YearMonthNumber. Then we create the rolling average measure:

Here’s the DAX measure I used:

OptimizedCumulativeAverage =
VAR SelectedDate = MAX('Calendar'[Date])
RETURN
CALCULATE(
AVERAGE('Roast'[Spec 430nm Colour]),
FILTER(ALL('Calendar'), 'Calendar'[Date] <= SelectedDate)
)

Using Calendar[YearMonth] in a slicer and this measure in a line chart or table, users can view dynamic rolling trends month by month.

Find attached .PBIX for your reference.

If the response has addressed your query, please Accept it as a solution and give a 'Kudos' so other members can easily find it

Best Regards,
Tejaswi.
Community Support Team.