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
Responsive Resident
Responsive Resident

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
Responsive Resident
Responsive Resident

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! 🙂

jennratten
Responsive Resident
Responsive Resident

You're very welcome!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors