cancel
Showing results for
Did you mean:
Helper I

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

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

1 ACCEPTED SOLUTION
Solution Supplier

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

3 REPLIES 3
Solution Supplier

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

Helper I

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

Solution Supplier

You're very welcome!

Announcements