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

[Juan_Quikin5]

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.

StartDate	EndDate	CampaignID
14/12/2022	30/12/2022	TRC-1
22/12/2022	13/01/2023	TRC-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! 

[rohit_singh · Accepted solution]

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

Output

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!