Forum Discussion

Whitney's avatar
Whitney
Icon for Helper II rankHelper 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
Icon for Helper II rankHelper II
5 years ago

Hi v-xulin-mstf, this is returning "(Blank)" in card view or nothing in a table. My DimDate table date range from 1/1/1900 -> 31/12/9999 if that makes a difference?

 

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()
)
lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
5 years ago

"My DimDate table date range from 1/1/1900 -> 31/12/9999 if that makes a difference?"

 

Consider the implications of this - If you have accidentally left the "Auto Date Time"  feature enabled this will create a date hierarchy for every single of your Date or Datetime fields, and will add about 3 Million ( MILLION !) rows for each of your date/ datetime columns.

 

Please always use a date range in your calendar table that just about covers your fact dates.