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/Retrospective-rolling-6-month-average-based-on-given-criteria/m-p/1810265#M704457 

 

The DAX I have used is marked as the solution in the above link. The next step and ultimate goal is to forecast WIP consumption using historical consumption as an indicator (see below picture and highlights for indication of chart and using historical WIP).

 

The card view is the rolling figure which I'd like to use in the future months highlighted - it is dynamic so it picks up fluctuations in the past 6 month period.

 

I've tried using this method but it keeps spitting back a number of different errors but it's effectively what I'm trying to achieve for WIP, not sales https://blog.enterprisedna.co/forecasting-techniques-in-power-bi-by-using-dax/ 

 

Any help is greatly appreciated!

Need Help

16 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

    Would you mind mentioning the errors you are experiencing?

    • Whitney's avatar
      Whitney
      Helper II
      5 years ago

      Hi, to get my total WIP hours, I tried to use:

      Total WIP hours =
      CALCULATE(SUM('Tracking Sheet'[Build Hours]),[Powertrain_WIP_Relative]="WIP")

      and it spits back "A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

      • lbendlin's avatar
        lbendlin
        Super User
        5 years ago

        Are you on the latest Power BI Desktop ? I thought that logic limitation had been lifted recently.

  • v-xulin-mstf's avatar
    v-xulin-mstf
    Community Support
    5 years ago

    Hi Whitney,

     

    Try measure as: 

    Total WIP hours = 
sumx(filter('Table',[Powertrain_WIP_Relative]="WIP")),'Tracking Sheet'[Build Hours])

     

    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
      Helper II
      5 years ago

      v-xulin-mstf thanks, this worked for the first calculation however, I'm now getting this error for the WIP forecast:


      'an invalid numeric representation of a date value was encountered'

      Total WIP Forecast =
      IF(ISBLANK([Total WIP Hours]),
      CALCULATE([Total WIP Hours],SAMEPERIODLASTYEAR('IDW DimDate'[Date])),
      BLANK() )
       
      however, my Date is a date value
      • v-xulin-mstf's avatar
        v-xulin-mstf
        Community Support
        5 years ago

        Hi Whitney,

         

        Please check:

        Does your date column contain an irregular date format? For example 'Year 9999'.

        If the issue persists, you can abandon the time-intelligent function and use the time function instead.

         

        Best Regards,
        Link

         

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