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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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