Forecasting graphs

[LCCKevO]

I have a data set that includes reserve balances. Another data set has forecasts for how these reserves are to be used. The forecast data set has 2 dates columns, the date the forecast was entered and the financial year that the forecast relates to. The forecasts relate to each of the next 3 years. We use the date the forecasts were entred to slice the data to show how the forecasts have changed over the year. I have various tables that show the information that I need.

However, as part of the reserve balances data set, we have a graph which shows how the balance has changed over the years. It would be nice to show this graph being extended to show what the balance would be if the forecast spend occured.

Due to the two different dates, I have used set up a bridging table for each financial year, and linked this to the calendar. Then I have set up an inactive relationship between financial year in the forecast data and bridging table. I've used USERELATIONSHIP to filter the data using the inactive relationship.  I now have a graph that looks like.

For the next step, I want to set up a calculation to show the cumulative value for the red line. However, everything I have tried doesn't show me the correct values. I either get the value just in the current year, or the calculation doesn't work, as I don't know how to use the USEREALTIONSHIP function in the time series functions I have used elsewhere.

Can anyone advise how I can do this, or if there is a simpler way of doing what I've already got?

[Anonymous]

Hi @LCCKevO ,

I'm a little confused about your needs, Could you please explain them further? It would be good to provide a screenshot of the results you are expecting and desensitized example data.

Thanks for your efforts & time in advance.

Best regards,
Community Support Team_ Binbin Yu

[LCCKevO]

Sorry for the delayed response, we had some network issues for 3 days, some example data is 

The Blue columns are ones that aren't in my data set. I've included these to help show what I want to achieve. Here I'm assuming that Reserve A has a balance of 30,000 and Reserve B has a balance of 20,000.

My model looks like

With relationships between DimDate(DateKey) and ReserveForecasts(PostingDate), and DimDate(Fin Year (short) and FinYear(Fin Year (short). There is an inactive relationship between ReservesForecast(Financial Year) and FinYear(Fin Year (short).

In order to get a forecast for each financial year I have used 

Forecast by Financial Year = CALCULATE('Reserve Forecasts'[Forecast £m],USERELATIONSHIP('Reserve Forecasts'[Financial year],'Fin Year'[Fin Year (short)]))

I want to be able to slice the report using the reserve name and posting date, to be able to show the grey line in the graph below. Which is Reserve A at 30/03/22.

The x axis being the Fin Year (Fin Year (short) field.

The "Forecast by Financial Year" calculation above, gives me the blue Iine. I think I need to calculate the Orange line, to show the cumulative forecast for use of the reserve. Then deduct this value from the balance at the end of the previous year (a value I already have from a different data set) to get the grey line.

Everything that I have tried so far, either gives me just a value in 2022/23 (which is 30,000), or fails because of an error in my DAX. 

Does that clarify what I'm trying to achieve.