Create row for every date in daterange and take average of metric

[basrooz]

Hi,

I have an Excel file showing the performance of several campaigns:

Start date	End date	Campaign	Cost
Jan 1 2021	Jan 4 2021	TV	1,000
Feb 1 2021	Feb 5 2021	Radio	20,000
Jan 1 2021		Paid search	500
Jan 2 2021		Paid search	500
Jan 3 2021		Paid search	500

For analysis purposes, I’d like to split all this data by date. That means that if no end date is given, nothing needs to happen. However, if an end date is given, then for each date between the start date and the end date I’d like to have a row created that’s evenly spreading the costs. Once that’s done, the end date column is no longer neccessary Output example::

Start date		Campaign	Cost
Jan 1 2021		TV	250
Jan 2 2021		TV	250
Jan 3 2021		TV	250
Jan 4 2021		TV	250
Jan 1 2021		Radio	4,000
Jan 2 2021		Radio	4,000
Jan 3 2021		Radio	4,000
Jan 4 2021		Radio	4,000
Jan 5 2021		Radio	4,000
Jan 1 2021		Paid search	500
Jan 2 2021		Paid search	500
Jan 3 2021		Paid search	500

I found this post https://community.powerbi.com/t5/Desktop/Create-rows-for-every-date-in-date-range/m-p/731818#M353041 that already explains how I can create a row for each date. But how do I exclude rows where no end date is given? And how do I make sure that the cost in this case is showing the average for each row instead of repeating the number?

Best regards

Bas

[jennratten · Accepted solution]

This is one way you can do this...

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJQ0gEyjeHsxIKCnNRiIMPUwEApVgdFHRAlJeYBIUjeCCQfCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StartDate = _t, EndDate = _t, Name = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"StartDate", type date}, {"EndDate", type date}, {"Name", type text}, {"Value", Int64.Type}}),
    date_diff = Table.AddColumn(#"Changed Type", "date_diff", each Duration.Days([EndDate] - [StartDate]), Int64.Type),
    remove_neg_dates = Table.SelectRows ( date_diff, each [date_diff] >= 0 or [date_diff] = null ),
    list_dates = Table.AddColumn ( remove_neg_dates, "NewColumn", each if [date_diff] = null then null else List.Dates(Date.From([StartDate]),[date_diff]+1,#duration(1,0,0,0))),
    expand = Table.ExpandListColumn(list_dates, "NewColumn"),
    change_type = Table.TransformColumnTypes(expand,{{"NewColumn", type date}}),
    remove_dates_diff = Table.RemoveColumns ( change_type, {"date_diff"} )
in
    remove_dates_diff

Before

After

[basrooz]

@jennratten that works perfect! Thanks a lot for your help! 🙂

[jennratten]

You're very welcome!