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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. 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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.