Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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

View solution in original post

1 REPLY 1
Anonymous
Not applicable

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.