cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
basrooz
Helper I
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
jennratten
Solution Supplier
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

jennratten_1-1633707013495.png

 

After

jennratten_2-1633707033159.png

 

 

View solution in original post

3 REPLIES 3
jennratten
Solution Supplier
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

jennratten_1-1633707013495.png

 

After

jennratten_2-1633707033159.png

 

 

View solution in original post

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

You're very welcome!

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors