cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Help on rolling average

Dear community,

I reproduced an issue I face on rolling average calculation:

I want to calculate a rolling average on 3 months in a table of 12 months and twelve values:

I have a calendar table.

Here is my basic table and the expected average calculated in Excel: so the first 20 is the average of 10,20,30 and etc...

Here is my formula of the rolling average:

average 3 month =
VAR lastmonth = MAX('Calendar'[Date])
VAR Last3MonthsAvgValue =
CALCULATE(
AVERAGEX(
DATESINPERIOD(
'Calendar'[Date],
lastmonth,
-3,
MONTH
),
[value test]
)
)
RETURN Last3MonthsAvgValue

Measure value test:

value test =
LOOKUPVALUE(
RAW[value],
RAW[month],
MAX('Calendar'[YearMonth])
)

RAW is my RAW table displayed above
Here is the result:

I don't understand why there are these tiny differences.

If someone has an idea that would be great.

Dominique

1 ACCEPTED SOLUTION
Helper I

Thanks Daniel, WINDOW function works fine in the table. Now my real dataset is not a table but a measure so it's a different approach. I will try to find the solution with what you gave me.

5 REPLIES 5
Helper I

Hi Daniel,

Here we go: .pbix and RAW file

https://we.tl/t-qzpgDgCZ6E

Tell me if you see something wrong.

Thanks a lot for your help.

Dominique

Super User

while still investigating the issue,

you can use this measure :

``````new measure =
var s =
SUMX(
WINDOW(
-2,REL,
0,REL,
SUMMARIZE(allselected(RAW),RAW[month],RAW[value]),
ORDERBY(RAW[month], ASC)
),
RAW[value]
)

return s / 3``````
Helper I

Thanks Daniel, WINDOW function works fine in the table. Now my real dataset is not a table but a measure so it's a different approach. I will try to find the solution with what you gave me.

Helper I

Sure but I can't see how to load the file

Super User

if possible, could you please the pbix file so i can take a closer look ?

best regards

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors