cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Anonymous
Not applicable

## Rolling average based on days and NOT sales value

Hi.

How do I create a rolling average for the last 12 months where the rolling average should be based on days and dates?

I wanto know the monthly average of Liggetid_dage based on the registered date or calendar date(I have a calendar table as well).

Best regards,

Lisa

3 REPLIES 3
Community Support

Hi @Anonymous ,

I created a calendar table which has YearMonthNumber and YearMonthName.

``````Calendar =
CALENDAR ( DATE ( 2021, 1, 1 ), DATE ( 2023, 12, 31 ) ),
"YearMonthNumber", VALUE ( FORMAT ( [Date], "YYYYMM" ) ),
"YearMonthName", FORMAT ( [Date], "YYYY-MMMM" )
)
``````

Then create a measure to get the rolling average for the last 12 months.

``````a rolling average for the last 12 months =
VAR _currentday =
MAX ( 'Calendar'[Date] )
VAR _end =
TODAY ()
VAR _start =
EOMONTH ( _end, -12 ) + 1
VAR _count =
CALCULATE (
DISTINCTCOUNT ( 'Calendar'[YearMonthName] ),
FILTER (
ALLSELECTED ( 'Calendar' ),
[YearMonthNumber] >= MAX ( 'Calendar'[YearMonthNumber] )
&& [Date] >= _start
&& [Date] <= _end
)
)
VAR _sum =
CALCULATE (
SUM ( 'Table'[Liggetid_dage] ),
FILTER (
ALLSELECTED ( 'Calendar' ),
[YearMonthNumber] >= MAX ( 'Calendar'[YearMonthNumber] )
&& [Date] >= _start
&& [Date] <= _end
)
)
RETURN
IF ( _currentday >= _start && _currentday <= _end, DIVIDE ( _sum, _count ) )
``````

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Hi @v-stephen-msft

I just tried your equations in my data set and I think that rolling average is not what I am looking for then because this number is way too high.
I just need a "normal" average that includes previous dates as well even if I limit the visual to 12 months - the average should be around 400-500 days. Can you help me with that?

Super User

@Anonymous , You need to have a measure like

12 Month Avg = CALCULATE(AverageX(Values('Date'[MONTH Year]),calculate(Sum('Table'[Value)))
,DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

Rolling Months Formula: https://youtu.be/GS5O4G81fww

You can also consider

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

#### Power BI Monthly Update - March 2024

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

Top Solution Authors
Top Kudoed Authors