Forum Discussion

aar21292's avatar
aar21292
Frequent Visitor
3 years ago
Solved

Creating a Forecasting Measure based on latest month measure

Hi

I have a measure which calculates turnover accumulation by month, and would like to add a new measure to forecast upcoming months. Ideally what I need is that for incomplete months, my forecast uses the information from the latest completed month, divides by that month's number and then multiplies by month number of upcoming months.

Example for the below data structure, I would use turnover in May (as it is the latest month with data) to calculate the rest of the months:

June = 10%/5*6 = 12%
July = 10% /5*7 = 14%
August = 10% /5*8 = 16%

 


Thanks in advance
Aaron

Need Help
Tips and Tricks
  • v-jianboli-msft's avatar
    v-jianboli-msft
    3 years ago

    Hi aar21292 ,

     

    Please Try:

     

    Turnover % Accumulated Forecast = 
var _a = SUMMARIZE(ALL('Measures Table'),'Measures Table'[Year],'Measures Table'[Month],"Value",[Turnover % Accumulated])
var _b = MAXX(_a,[Value])
var _c = CALCULATE(DISTINCTCOUNT('Measures Table'[Month]),FILTER(ALL('Measures Table'),[Year]=SELECTEDVALUE('Measures Table'[Year])&&[Month_Number]<=SELECTEDVALUE('Measures Table'[Month_Number])&&[Turnover %]<>BLANK()))
var _d = CALCULATE(DISTINCTCOUNT('Measures Table'[Month]),FILTER(ALL('Measures Table'),[Year]=SELECTEDVALUE('Measures Table'[Year])&&[Month_Number]<=SELECTEDVALUE('Measures Table'[Month_Number])))
return IF(ISBLANK(SELECTEDVALUE('Measures Table'[Turnover %])),DIVIDE(_b,_c)*_d)

     

    Best Regards,

    Jianbo Li

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

8 Replies

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi aar21292 ,

     

    Based on your description, I have created a simple sample:

    Please try:

    Turnover % Accumulated Forecast = 
var _a = SUMMARIZE(ALL('Table'),[Year],'Table'[Month],'Table'[Month_Number],"Value",[Turnover % Accumulated])
var _b = MAXX(_a,[Value])
var _c = CALCULATE(DISTINCTCOUNT('Table'[Month]),FILTER(ALL('Table'),[Year]=SELECTEDVALUE('Table'[Year])&&[Month_Number]<=SELECTEDVALUE('Table'[Month_Number])&&[Turnover %]<>BLANK()))
var _d = CALCULATE(DISTINCTCOUNT('Table'[Month]),FILTER(ALL('Table'),[Year]=SELECTEDVALUE('Table'[Year])&&[Month_Number]<=SELECTEDVALUE('Table'[Month_Number])))
return IF(ISBLANK(SELECTEDVALUE('Table'[Turnover %])),DIVIDE(_b,_c)*_d)

    Final output:

    Best Regards,

    Jianbo Li

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

    • aar21292's avatar
      aar21292
      Frequent Visitor
      3 years ago

      Hi Jianbo Li,

      Thanks for looking into this.


      The only issue I am facing is for the last line in the formula
      I am getting this error: Column "Turnover %" cannot be found or may not be used in this expression.


      It's probably because the year, month, month_number fields are in one table. And the turnover % field is in a different table. Any idea how to fix this please?

      Thanks
      Aaron

      • v-jianboli-msft's avatar
        v-jianboli-msft
        Icon for Community Support rankCommunity Support
        3 years ago

        Hi aar21292 ,

         

        Is there any relationship between the data used in the visual and the table to which the turnover % belongs?

        Sorry for that the information you have provided is not making the problem clear to me. Can you please share more details to help us clarify your scenario?

        Please provide me with more details about your table or share me with your pbix file after removing sensitive data.

         

        Refer to:

        How to provide sample data in the Power BI Forum

        How to Get Your Question Answered Quickly

         

        Best Regards,

        Jianbo Li

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