Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Forward Forecast (Moving Average) based on Cumulative Count Measure

Hi everyone, I've created a measure that calculates the cumulative count of hires and terminations in order to calculate the headcount (hires minus terminations):   HiredStaff = CALCULATE ( ...
Need Help
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    Hi Anonymous 

    Thanks for your reply.

     

    I find the formula it uses involves recursion. However, DAX language doesn't support recursion.

     

     

    Best Regards,

    Community Support Team _Tang

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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

thanks v-xiaotang !

 

I didn't even realize that there is something wrong with the measure [MovAvg_Headcount]. I thought it works, but thanks for letting me know.

 

[MovAvg_Headcount] calculates the moving average for the headcount (meaning the historic data). Would you also have an idea on how to create another measure (or calculated column) to calculate the moving average forecast for headcount (like what I did in Alteryx)? I actually don't really need the moving average for the historic headcount. I just created the [MovAvg_Headcount] measure, hoping that it would help to calculate the moving average forecast.

v-xiaotang's avatar
v-xiaotang
Icon for Community Support rankCommunity Support
4 years ago

Hi Anonymous 

I have not used this software, so not quite understand the formula Alteryx uses to calculate the moving average forecast for headcount. Can you describe how the result you want is calculated? If you do it manually instead of using Alteryx.

 

Best Regards,

Community Support Team _Tang

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    hi v-xiaotang ,

     

    I've added an excel file (Formula.xlsx) with a formula that calculates the moving average forecast in Excel to the dropbox folder: https://www.dropbox.com/sh/2kw6iplladj84a3/AABJ3N6vfeyw-JxEddf2Hvl-a?dl=0

     

    Here you can see a screenshot of the formula I used in Excel:

    This is the formula:

    =ROUND(IF(H28="";AVERAGE(IF(H27="";J27;H27);IF(H26="";J26;H26);IF(H25="";J25;H25);IF(H24="";J24;H24);IF(H23="";J23;H23);IF(H22="";J22;H22));"");5)

    I would like to recreate a similar formula in Power BI to calculate the moving average forecast in my dashboard. I hope this makes it clearer. If not, please let me know and I'll provide more detailed information.

     

    Best,

    Matisse

    • v-xiaotang's avatar
      v-xiaotang
      Icon for Community Support rankCommunity Support
      4 years ago

      Hi Anonymous 

      Thanks for your reply.

       

      I find the formula it uses involves recursion. However, DAX language doesn't support recursion.

       

       

      Best Regards,

      Community Support Team _Tang

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        hi v-xiaotang ,

         

        does this mean it's impossible to calculate the moving average forecast in Power BI?

         

        I get an error message if I try to open your file:

         

        Seems my Power BI version is outdated. Unfortunately, I cannot update it myself. I need to wait for my company to do this. Is there a way I can solve this?

         

        Best,

        Matisse