Forum Discussion

jennriches's avatar
jennriches
Frequent Visitor
3 years ago

Cumulative measure for forecasting not rolling forward

I am completely stumped on this!

 

I have a measure [Budget (Opening/Forecast Open)] which should display actualised figures and forecast figures if the date is in the future.

 

Below is an example, I want the opening figure for December to be £926,536,731.66

Opening for November  + Market Growth in November + Withdrawals in November + New Business in November

And this is working our correctly, however, it is not then cumulatively adding on the adjustments for the following month. So for January, I want it to show £936,890,681.03 as the opening figure:

 

December Opening + December Market Growth + December Withdrawals + December New Business

 

It seems to be stuck on the calculation based on November's figures and not moving forward. 

 

 

 

This is my measure for [Budget (Opening/Forecast Open)] =

 


VAR LastDateFUM = CALCULATE(LASTDATE(PCRollingData[Date]), ALLEXCEPT('Date','Date'[Monthly]))

VAR MonthsDiff = CALCULATE( DATEDIFF(SELECTEDVALUE('Date'[Date]),LastDateFUM,MONTH))

VAR Opening = IF( ISBLANK( [FUM (Chargeable)]), CALCULATE([FUM (Chargeable)], DATEADD( 'Date'[Date], MonthsDiff, MONTH)), [FUM (Chargeable)])

VAR MarketAdjustment = CALCULATE( Opening * 'Budget (Market Movement)'[Budget (Market Movement) Value])

VAR WithdrawalAdjustment = CALCULATE( Opening * 'Budget (Withdrawals)'[Budget (Withdrawals) Value]) *-1

VAR NewBusinessAdjustment = CALCULATE ( SUM( Budget[Value]), Budget[Type] = "FUM")

VAR Adjustments = MarketAdjustment+WithdrawalAdjustment+NewBusinessAdjustment

RETURN
IF( ISBLANK( [FUM (Chargeable)] ) , Opening + CALCULATE( Adjustments, DATEADD('Date'[Date], MonthsDiff, MONTH)) , Opening)
 

 


Any advice is appreciated - thank you!

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi jennriches ,

     

    Do you want future dates to return the value of the latest month?

    Then you want November 2022, although the actual value, but you want to return the forecast value, right?

    If so, try filtering the date filter to the current month cut-off, or to the month before the current month.

    If I can, provide some suggested dummy data and desired results, and I'll try to reproduce it.

     

    Best Regards,

    Stephen Tao

     

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

    • jennriches's avatar
      jennriches
      Frequent Visitor
      3 years ago

      Hi Stephen

       

      Thank you for coming back to me, I think I may have overcomplicated my question. 

      I believe my biggest issue is with a circular reference between the Opening and the Forecast figure.

       

      I have included a OneDrive link to an excel sheet, this is what I am trying to achieve in Power Bi.  

       

      Link to my excel table 

       

      I have also created a pbix file with some dummy data, and the OneDrive link is below:

       

      OneDrive file for PBIX dummy data 

      I have simplified the measures I was trying to use as I think the main issue i have is the circular reference for the Opening measure as this is trying to use the forecast measure.

       

      Thank you in advance for any help!