Forum Discussion

LCCKevO's avatar
LCCKevO
Frequent Visitor
3 years ago

Forecasting graphs

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 an...
Need Help
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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's avatar
LCCKevO
Frequent Visitor
3 years ago

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.