cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Juan_Quikin5
Regular Visitor

Count the number of campaigns on a date based on start and end dates

Hey guys! I hope you can help me with some issues. 

 

I have a table of active advertising campaigns with three columns: start date, end date, and de campaign ID.

I also have a date table.

But these two tables have no relationship in the model.

 

What I would like is to count the number of active campaigns on a certain date.

 

StartDateEndDateCampaignID
14/12/2022  30/12/2022  TRC-1
22/12/2022 13/01/2023TRC-2

 

In this example, the number of active campaigns on 20/12/2022 is 2 and on 04/01/2023 is 1.

 

Any help would be very appreciated! 

1 ACCEPTED SOLUTION
rohit_singh
Super User
Super User

Hi @Juan_Quikin5 ,

You could try something like this in Power Query. Please open a blank query--> Advanced editor-->Remove any existing code and copy and paste the below code.

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTRNzTSNzIwMlJQUNJRMjZA4YYEOesaKsXqRCsZGcElgOKGxvoGhiCeMVSRkVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StartDate = _t, EndDate = _t, CampaignID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"StartDate", type date}, {"EndDate", type date}, {"CampaignID", type text}}),
    #"Added Date" = Table.AddColumn(#"Changed Type", "Date", each {Number.From([StartDate])..Number.From([EndDate])}),
    #"Expanded Date" = Table.ExpandListColumn(#"Added Date", "Date"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Date",{{"Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"Date"}, {{"CampaignCount", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

 

 

Input

rohit_singh_0-1670004549564.png

Output

rohit_singh_1-1670004568802.png

You just need to add a new column that has a list of all dates between start and end days, and then expand them into new rows. Finally perform a group by operation on the new date column and count the number of rows, which is the number of active campaigns on a given day.

Kind regards,

Rohit


Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos!

View solution in original post

1 REPLY 1
rohit_singh
Super User
Super User

Hi @Juan_Quikin5 ,

You could try something like this in Power Query. Please open a blank query--> Advanced editor-->Remove any existing code and copy and paste the below code.

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTRNzTSNzIwMlJQUNJRMjZA4YYEOesaKsXqRCsZGcElgOKGxvoGhiCeMVSRkVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StartDate = _t, EndDate = _t, CampaignID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"StartDate", type date}, {"EndDate", type date}, {"CampaignID", type text}}),
    #"Added Date" = Table.AddColumn(#"Changed Type", "Date", each {Number.From([StartDate])..Number.From([EndDate])}),
    #"Expanded Date" = Table.ExpandListColumn(#"Added Date", "Date"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Date",{{"Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"Date"}, {{"CampaignCount", each Table.RowCount(_), Int64.Type}})
in
    #"Grouped Rows"

 

 

Input

rohit_singh_0-1670004549564.png

Output

rohit_singh_1-1670004568802.png

You just need to add a new column that has a list of all dates between start and end days, and then expand them into new rows. Finally perform a group by operation on the new date column and count the number of rows, which is the number of active campaigns on a given day.

Kind regards,

Rohit


Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos!

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.