Remaining Budget Calculation (Days)

[adarshalok]

Hi Everyone, 

I have a table where I have the follwoing details: 

Project Name	Project Start Date	Project End Date	Budget (In $)
P1	1/1/2023	4/30/2023	10000
P2	1/1/2023	5/30/2023	50000
P3	1/1/2023	6/30/2023	60000
P4	1/1/2023	12/12/2023	100000

I would like to show how much of my budget are remaining as of the current date (Budget gets split into number of days until project ends and as the date progresses, the budget remaining should show the available budget (Excluding the $ amount which has already been billed into the project).

Eg: P3 is for 6months aka frm 1/1/23 to 6/30/23 and has a budget of $60000.  As of 1/1/23, the remaining budget is $60,000. As of 3/30/23. the remaining budget now is ~$30,000. 

Any help would be appreciated. 

[adudani · Accepted solution]

Hi @adarshalok ,

create a blank query and paste the following code:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz0pNLlHwS8xNVdKBc4NLEotKFFwSS5AFXfNSYEJOpSnpqSUKGp55CiqaSrE6QIMMgcKG+ob6RgZGxkCmib6xAYxtaAAEEFVGqKpMkVSZIlQZo6oyQ1JlhlBlgqrK0EgfiJCtBKqLBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Project Name", type text}, {"Project Start Date", type date}, {"Project End Date", type date}, {"Budget (In $)", type number}}),
ProjectDuration = Table.AddColumn(#"Changed Type1", "ProjectDuration", each Duration.Days([Project End Date] - [Project Start Date])+1, Int64.Type),
DailyBudget = Table.AddColumn(ProjectDuration, "DailyBudget", each [#"Budget (In $)"]/[ProjectDuration]),
#"Changed Type2" = Table.TransformColumnTypes(DailyBudget,{{"DailyBudget", type number}}),
Custom1 = Table.AddColumn(#"Changed Type2", "#DaysinProjectCompleted", each Duration.Days(Date.From(DateTime.FixedLocalNow()) - [Project Start Date])+1, Int64.Type),
#"Amount Billed Including today" = Table.AddColumn(Custom1, "Amount Billed Including today", each [DailyBudget]*[#"#DaysinProjectCompleted"]),
#"Changed Type3" = Table.TransformColumnTypes(#"Amount Billed Including today",{{"Amount Billed Including today", type number}}),
BudgetLeft = Table.AddColumn(#"Changed Type3", "BudgetLeft", each [#"Budget (In $)"]-[Amount Billed Including today]),
#"Changed Type4" = Table.TransformColumnTypes(BudgetLeft,{{"BudgetLeft", type number}})
in
#"Changed Type4"

This is the final output.

The intermediate steps can be reduced if this meets the requirement.

Appreciate a thumbs up if this helps.

Please accept this as the solution if the question is resolved.