Actual vs Forecast Visual Help

[tgibble]

Power BI Community,

I am trying to create a visual (clustered column chart) that shows actuals and forecasted values by month.  The table I has contains the following information:

(Each saving is one item with the following data)
Savings Name
Start Date
End Date
Actual annual savings
Forecasted annual savings

Can I do this with the data I have?  I am not sure how to take the annual savings a break it up by month.  

Thanks,

Tim

[Ashish_Mathur]

Hi,

Share data in a format that can be pasted in an Excel file.

[tgibble]

I think I need to use measures but I not sure what the function would be.  

Below is an example of the data I have:

Name	Annual Savings	Monthly Savings	Start Date	End date
AV Savings	$            120,000	$                 10,000	1/1/2019	12/31/2019
Housekeeping Savings	$              20,000	$                   1,667	3/1/2019	2/28/2020
Lab Savings	$              11,000	$                       917	4/1/2019	3/30/2020

How can I get a measure to take the monthly savings and divide by month using the start and end date, and then add all the savings that occur in each month.  For example, the clustered column would have $10k for January 2019, $10k for February, $11.6k for March, and $12.5k for April.  I also want to function to not add the monthly savings for a particular project after the end date is reached.  

Thanks,
Tim

[Ashish_Mathur · Accepted solution]

Hi,

Please download my PBI file from here.

Hope this helps.