Spread Budget between Start Date and End Date

[geremymsantos]

Hello.

I'm trying to build out a report that shows a budget spread, grouped by month in a visual, over the life of a campaign between the start and end date. I am using a table that contains the budget amount, a start date and an end date. The following screenshot is an example of what I'm working with.

Important Notes:

- Start and end dates can be any days within a month, not just the first or last day

- As such, the budget does not spread evenly per month but it does per day. Based on the screenshot above, Campaign A would only have 19 days in January to allocate part of the budget whereas February and March would contain all days within that month.

The output should look something like this:

Any direction on how to get started would be great.

Thanks a bunch!

[v-juanli-msft]

Hi @geremymsantos

Open Queries Editor

create a new blank query called "query1", paste the code in advanced editor

let
    Source = List.Dates(#date(2018, 1, 1), 365, #duration(1, 0, 0, 0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Sorted Rows" = Table.Sort(#"Converted to Table",{{"Column1", Order.Ascending}}),
    #"Renamed Columns" = Table.RenameColumns(#"Sorted Rows",{{"Column1", "Date"}}),
    #"Inserted End of Month" = Table.AddColumn(#"Renamed Columns", "End of Month", each Date.EndOfMonth([Date]), type date),
    #"Added Conditional Column" = Table.AddColumn(#"Inserted End of Month", "if =", each if [Date] = [End of Month] then [Date] else null),
    #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each [#"if ="] <> null and [#"if ="] <> ""),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"End of Month", "if ="})
in
    #"Removed Columns"

save this table as a excel file in my server and get a file path

campaign	budget	start date	end date
a	100000	1/13/2018	4/15/2018
b	250000	2/14/2018	5/2/2018
c	67000	4/27/2018	8/22/2018

then create a new blank query and use the file path(bold character) in the following code

let
    Source = Excel.Workbook(File.Contents("C:\Users\maggiel\Desktop\case\1\1.26\1.26.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"campaign", type text}, {"budget", Int64.Type}, {"start date", type date}, {"end date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "total days", each Duration.TotalDays([end date]-[start date])+1),
    #"Inserted End of Month" = Table.AddColumn(#"Added Custom", "End of Month", each Date.EndOfMonth([start date]), type date),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted End of Month",{{"End of Month", "End of star Month"}}),
    #"Inserted End of Month1" = Table.AddColumn(#"Renamed Columns", "End of Month", each Date.EndOfMonth([end date]), type date),
    #"Renamed Columns1" = Table.RenameColumns(#"Inserted End of Month1",{{"End of Month", "End of end Month"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns1", "dates list", each Query1[Date]),
    #"Expanded dates list" = Table.ExpandListColumn(#"Added Custom1", "dates list"),
    #"Added Custom2" = Table.AddColumn(#"Expanded dates list", "filter", each if [dates list]>=[End of star Month] and [dates list]<=[End of end Month] then [dates list] else null),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom2", each [filter] <> null and [filter] <> ""),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"End of star Month", "End of end Month", "dates list"}),
    #"Added Custom3" = Table.AddColumn(#"Removed Columns", "days each month", each Duration.TotalDays([filter]-[start date])+1),
    #"Added Index" = Table.AddIndexColumn(#"Added Custom3", "Index", 1, 1),
    #"Added Custom4" = Table.AddColumn(#"Added Index", "Custom", each #"Added Index"{[Index]-2}[days each month]),
    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "days each month2", each if [days each month]-[Custom]>0 then [days each month]-[Custom] else [days each month]),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Added Custom5", {{"days each month2", 19}}),
    #"Added Custom6" = Table.AddColumn(#"Replaced Errors", "allocate", each [budget]/[total days]*[days each month2]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom6",{"days each month", "Index", "Custom"}),
    #"Added Custom7" = Table.AddColumn(#"Removed Columns1", "month-year", each Text.Combine({Date.MonthName([filter]), Text.From(Date.Year([filter]), "en-US")}, "-")),
    #"Pivoted Column" = Table.Pivot(#"Added Custom7", List.Distinct(#"Added Custom7"[#"month-year"]), "month-year", "allocate"),
    #"Filled Down" = Table.FillDown(#"Pivoted Column",{"January-2018", "February-2018", "March-2018", "April-2018", "May-2018", "June-2018", "July-2018", "August-2018"}),
    #"Added Custom8" = Table.AddColumn(#"Filled Down", "Custom", each Function.Invoke((a)=>List.Max(Table.SelectRows(#"Filled Down",each [campaign]=a)[filter]),{[campaign]})),
    #"Added Conditional Column" = Table.AddColumn(#"Added Custom8", "condition", each if [filter] = [Custom] then 1 else null),
    #"Filtered Rows1" = Table.SelectRows(#"Added Conditional Column", each [condition] <> null and [condition] <> ""),
    #"Removed Columns2" = Table.RemoveColumns(#"Filtered Rows1",{"condition"})
in
    #"Removed Columns2"

finally close &&apply, you will get the table above

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.