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
v-xulin-mstf's avatar
v-xulin-mstf
Icon for Community Support rankCommunity Support
5 years ago

Hi Whitney

 

You can abandon the time-intelligent function and use the time function instead.

Try measure as:

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() 
)

 

Best Regards,
Link

 

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

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()
)
  • v-xulin-mstf's avatar
    v-xulin-mstf
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi Whitney

     

    The year '9999' caused the issue.

     

    Best Regards,
    Link

     

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

  • 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.

  • Whitney's avatar
    Whitney
    Icon for Helper II rankHelper 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
    Icon for Community Support rankCommunity 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.