Moving Average data past the end of the data

[Anonymous]

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? 

Thanks by advance, 

[jstorm]

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 · Accepted solution]

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 !

[amitchandak]

Change var like and try

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

[Anonymous]

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