Forum Discussion

Whitney's avatar
Whitney
Helper II
5 years ago

Rolling average forecast

Hi,   I received great assistance on this thread however, I can't figure out how to use the solution to create the forecast I desire in future months.   https://community.powerbi.com/t5/Desktop/R...
Need Help
Whitney's avatar
Whitney
Helper II
5 years ago

Hi v-xulin-mstf , I have filtered the data in the transformation space to be 1/1/2020 - 31/12/2022. It is a company dataset and I cannot change the source.

This formula is still returning blank after filtering the dates - any ideas?

 

Total WIP Forecast =
var maxdate = MAX('IDW DimDate'[Date])
var mindate = DATE(YEAR(maxdate)-1,MONTH(maxdate),DAY(maxdate))
return
IF(
ISBLANK([Total WIP Hours]),
CALCULATE( [Total WIP Hours], FILTER( 'IDW DimDate', 'IDW DimDate'[Date]<maxdate && 'IDW DimDate'[Date]>mindate ) ),
BLANK()
)
 
v-xulin-mstf's avatar
v-xulin-mstf
Community Support
5 years ago

Hi Whitney,

 

Maybe you can try your orginal measure again:

Total WIP Forecast =
IF(ISBLANK([Total WIP Hours]),
CALCULATE([Total WIP Hours],SAMEPERIODLASTYEAR('IDW DimDate'[Date])),
BLANK() )

 

Best Regards,
Link

 

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