Creating a Chart to Show Financial Saves over a 12-Month Period After a Completion

[mkusler]

I have a simple data table with the following information. 

Is there a way in PowerBI I can automatically create a bar chart from this data that projects out the cumulative saves over the next 12 months from the "Completion Date", so I would end up with a chart similar to this.

It's not as important to be able to see each individual project, simply the cumulative save for all projects distributed over 12 months after project completion. 

I hope that makes sense. 

[ryan_mayu · Accepted solution]

that will create automatically, you can change the data source , add more data to check

[ryan_mayu]

@mkusler 

here is a workaround for you. pls see the attachment below

[mkusler]

@ryan_mayu 

Is there a way the table in the PBIX file you provided can be created automatically based on the 3 bits of information I provided (Project Name, 12-Month Save and Completion Date? If I have to manually create it, there is no point to using PowerBI.

[ryan_mayu · Accepted solution]

that will create automatically, you can change the data source , add more data to check

[mkusler]

@ryan_mayu Thank you! I took another look at Power Query and saw what you did. Very cool. Thanks.

[ryan_mayu]

you are welcome

[mkusler]

@ryan_mayu 

Me again....I took a closer look and noticed that there was an issue with the dates going 12-Months backwards and not forwards. I looked at Power Query and saw that on step "Added Custom" the dates for my sample data are coming up a year behind. 

I tried making some modifications, but everything I did would break the entire query and I'm not sure what to do. Other than that, everything is working, I just need it to forecast the divided out 12-Month save forwards, not backwards in time. 

Any thoughts on how I can fix this?

[ryan_mayu · Accepted solution]

@mkusler 

pls see the attachment below

[mkusler]

@ryan_mayu Thank you! Now that I look through everything, I see how simple it was. I had changed one value, but not the other which was causing errors. I appreciate all the help as I will probably be able to use these concepts I've learned now in future reports.

[ryan_mayu]

you are welcome

[mkusler]

@ryan_mayu 

The solution you had provided me has worked fine since we figured it out in July. However, I just encountered an error during refresh. When I went into Power Query I see that I start getting errors durring the "Added Custom" step. As you can, see it's returning an error in the data. The interesting thing is that this is only happening for projects with a December 2023 completion date. Any reason why this is happening and how I can fix it?

Here is the error I get.

Here are all the steps that were applied. 

[ryan_mayu]

@mkusler 

pls try this

Date.AddDays( Date.AddMonths([completion date],-11),+1)

[mkusler]

@ryan_mayu Thank you for the fast response! I will check your solution out as well, but I started thinking and then I realized that if the "Completion Date" is in December (which corresponds to month 12), adding 1 to it would result in 13, which is not a valid month.

So....I came up with this and it seems to work.

let
// original expression
originalDate = #date(Date.Year([Completion Date])-1, Date.Month([Completion Date])+1, 1),

// Adjusted expression to handle December
adjustedDate = if Date.Month([Completion Date]) = 12 then #date(Date.Year([Completion Date]), 1, 1)

else
originalDate

in
adjustedDate

I really do appreciate all the help!