Actuals and Forecast data with historic monthly data and updated forecasts

[Stuffit25]

Hey All,
Long time user short term joiner!

I have loads of data but its all in a non standardised way of working.

i have tried to combine one projects Actual and forecast data (attached with dummy data) in a data model i thought would be fit for purpose but im struggling.

Each month a new version of the Forecast is provided up until the projects end of lifecycle with a new update of what the previous months actuals were but i'm having trouble creating measures for me to use the most recent months reporting period and using last months "in month forecast" & last months "EAC" for example to compare variance analsysis to the most recent months actuals...

What i want to showcase is a bar chart / line graph showcasing the actual and forecast data by month with the "reporting month" as a filter. In essence i have created a database of moving data points so as time moves on a new reporting month is created with a whole new set of forecasts depending on the previous months spend and/or a new Estimate to Complete value.

I then also want a table to showcase actuals by year and by month and forecasts by year and month.

what seems to be happening right now is that it is adding up all my actuals and all my forecasts all together rather than being seperated out by reporting period and forecast month

Any thoughts?

[GuillaumePower · Accepted solution]

I've done a project for this (below)

I'm not sure to  well understand what you need, I guess that you would like compare a month for a year against another  month, perhaps the same month with another year. For that you can use a time intelligence function to show a line with a delta ot time in front of a line.

You'll have to use a date table for that, if you have a table with your Forecast with the new value, you can compare on the same date or with another date with the function DATEADD(), whith  this function you'll be able to swith from  a month, or a quarter in front of the actual period. And you 'll be able to use the funcion minus between column in the same line. 

I give you the projet by Google Drive.

https://drive.google.com/file/d/1e49s3qM3KhasnYgkffQyw6GNrg9YF9oe/view?usp=sharing