cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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:

How do I get the rolling average for each month?

Thank you!

1 ACCEPTED SOLUTION
New Member

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

New Member

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

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors