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

Anonymous
Not applicable

## Moving Average data past the end of the data

Hello,

I have a measure to calculate a moving average on 5 days, this works fine.

However, PBI seems to forecast my data past the end of my date max, of my last data.

The last datapoint I have is in 25/04/2020 but my average is going until the 30/04/2020. I want my data to stop on the 25/04/2020.

I have two tables : a calendar + a factable. My calendar is used for lots of other tables so its Max and Min doesn't depends on my factable.

My measure to calculate the moving average is :

moving_average =
var __workingdays = 5
var __currentday = MAX(dim_date[Date])

var __daystoavg =
TOPN(__workingdays ;
CALCULATETABLE(
dim_date ;
dim_date[Date] <= __currentday ;
dim_date[Flg_JourOuvre] = 1 ;
ALL(dim_date)
) ;
dim_date[Date] ; DESC
)

var __avg =
DIVIDE(
CALCULATE(
SUM(FactTable[montant]) ;
__daystoavg
) ;
COUNTROWS( __daystoavg )
)

return
__avg

Can anyone tell me how PowerBI is calculating these extra values? and why it is calculating them?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Yes, results are correct but I do not want the 5 last dates. I've the all(dimdate) in my formula.

Actually I find a solution  :

in my date table, I create a calculated column = max(factable[Date])

and then create a measure :

flg_max =
IF(MAX(dim_date[max_factable]) >= MAX(dim_date[Date]) ; 1 ; 0 )

I put this measure on the visual's filter = 1 and it's working fine !
4 REPLIES 4
Resolver III

It is performing as expected. Look at the calculations:

54+25+14+74+0 = 167

167/5 = 33

25+14+74+0+0 = 113

113/5 = 23

14+74+0+0+0 = 18

Your calulation is looking at all dates in your date table. You need to limit your selection by dates that exist in your fact table.  I'm wondering why you have the ALL() filter in __daystoavg.

Anonymous
Not applicable

Yes, results are correct but I do not want the 5 last dates. I've the all(dimdate) in my formula.

Actually I find a solution  :

in my date table, I create a calculated column = max(factable[Date])

and then create a measure :

flg_max =
IF(MAX(dim_date[max_factable]) >= MAX(dim_date[Date]) ; 1 ; 0 )

I put this measure on the visual's filter = 1 and it's working fine !
Super User

Change var like and try

var __currentday = MAXX(allselected(dim_date),dim_date[Date])

Anonymous
Not applicable

thnks but it's not working : if I change this variable, nothing appears anymore in my "moving_average" column , everything's blank ..

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.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors