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

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.

    • aar21292's avatar
      aar21292
      Frequent Visitor
      3 years ago

      Hi Jianbo Li

      So basically I have separate tables for turnover measures and date measures.
      Therefore the formula you provided does not seems to work when it tries to use calculate fields from different tables.
      Here is the link to the Sample File of how my data is structured.

      Should I have all my measures in the same table?

      Thanks
      Aaron

      • v-jianboli-msft's avatar
        v-jianboli-msft
        Icon for Community Support rankCommunity Support
        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.