Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Juan_Quikin5
Frequent 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
Solution Sage
Solution Sage

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
Solution Sage
Solution Sage

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.