cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
daxN
New Member

How to create rolling average from a measure

I have a measure: 'Monthly sales count'

This measure is calculated from `Monthly sales` table that has an established with `MyDate` table.

`MyDate` table has a column Month to retrieve month name from date.

Columns in `MyDate' table: `SalesDate` in mm/dd/yyyy formate, and `Month` with months name.

 

Here's the DAX i came up with to calulate 2 months of Rolling average:

 

RA =
VAR CurrentDate = LASTDATE('MyDate'[SalesDate])
VAR ThreeMonthsAgo = EDATE(CurrentDate, -3)
RETURN
    AVERAGEX(
        FILTER(
            'MyDate',
            'MyDate'[SalesDate] >= ThreeMonthsAgo && 'MyDate'[SalesDate] <= CurrentDate
        ),
        CALCULATE([Monthly sales count])
    )

Here's the result I get:

daxN_0-1686244567577.png

How do I get the rolling average for each month?

Thank you!

1 ACCEPTED SOLUTION
daxN
New Member

Solved:  CALCULATE([Measure],DATESINPERIOD( 'Date'[Date], MIN( 'Date'[Date] ) -1, -3, MONTH ))

View solution in original post

1 REPLY 1
daxN
New Member

Solved:  CALCULATE([Measure],DATESINPERIOD( 'Date'[Date], MIN( 'Date'[Date] ) -1, -3, MONTH ))

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors