How to calculate the number of Campaigns running based on the start and end dates?

[give1ngaymai]

In the limited knowledge of Dax and power query functions, I still have not found a way to calculate the number of Campaigns running based on the start and end dates.

I have a table with sample data as follows:

Campaign name	Start date		End date
Campaign A	2022-07-20		2022-11-17
Campaign B	2022-08-16		2022-10-10
Campaign C	2022-07-23		2022-11-17
Campaign D	2022-08-10		2022-11-09
Campaign E	2022-07-06		2022-09-07
Campaign F	2022-08-06		2022-10-06
Campaign G	2022-08-24		2022-09-18
Campaign H	2022-07-14		2022-11-23
Campaign I	2022-07-28		2022-11-20
Campaign J	2022-08-04		2022-09-24
Campaign K	2022-08-03		2022-11-18
Campaign L	2022-07-30		2022-10-29
Campaign M	2022-07-12		2022-09-17
Campaign N	2022-08-08		2022-10-29
Campaign O	2022-08-11		2022-09-27
Campaign P	2022-08-08		2022-09-07
Campaign Q	2022-07-18		2022-09-06

I want to calculate how many campaigns are happening on 11 Aug (for example) or similar any day I want to see how many campaigns are running. Can you suggest to me some solutions?

[Ethan96 · Accepted solution]

Hi, @give1ngaymai 

Add a seperate calendar table and try this:

Count =
CALCULATE (
    COUNT ( 'Table'[Campaign name] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Start date] <= SELECTEDVALUE ( 'Calendar'[Date] )
            && 'Table'[End date] >= SELECTEDVALUE ( 'Calendar'[Date] )
    )
)

Sample file

Regards,

Ethan Fang

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[MattAllington]

You can use the technique I cover here. It's the same problem 

https://exceleratorbi.com.au/how-many-employees-by-period/