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 ,

 

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.

    • aar21292's avatar
      aar21292
      Frequent Visitor
      3 years ago

      Hi Jianbo li,

      As I mentioned before, the formula you suggested works if all the measures are in the same table. In my case, the [year], [month], [month number] measure fields are in a different table.

      Thanks
      Aaron

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

        Hi aar21292 ,

         

        The Measure Table and Date Table do not have any relationship with each other.

        Additionally, the 'Year' and 'Month' used in the visual are both from the Measure table.

        Therefore, you should not replace the [year], [month], and [month number] in the measure with data from the Date table.

         

        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.