Count between two dates in slicer

[Al_Padrini]

Hi all,

I am trying to create a card visual that counts the number of current active destinations between launch and cease dates, as definied by a 'Between' date slicer, as below.

The data source I'm using is below. A blank cease date means that the destination is still serviced and it should be counted. 

City name	Launch date	Cease date (if applicable)
Paris	1/06/2021
Dallas	15/09/2021
Amsterdam	4/12/2021
Tokyo Narita	13/05/2022	5/01/2024
Bogota	5/06/2022
New York City	17/08/2022	24/03/2023
Rome	29/08/2022
Lagos	9/12/2022
Shanghai	30/12/2022	17/02/2024
Frankfurt	20/01/2023
Madrid	7/07/2023
Amsterdam	16/08/2023
Jakarta	24/03/2024
Beijing	6/06/2024
Istanbul	12/06/2024
Singapore	7/08/2024
Mexico City	21/08/2024
Sao Paulo	28/08/2024
Dubai	21/09/2024
Bangkok	28/10/2024

Using the table and slicer above as an example, my card here would return 9 current destinations. That's the 12 that have been launched between June 1, 2021 and March 11, 2024, minus the three that have been ceased (Tokyo, NYC, Shanghai).

Thank you for your help.

Al

[Idrissshatila]

Hello @Al_Padrini ,

use the following measure 

Measure = CALCULATE(COUNT('Table'[City name]),'Table'[Cease date] = BLANK())

[Al_Padrini]

Hi @Idrissshatila,

Thank you, but it looks like it calculates the rows that correspond to the conditions 'cities that have been launched' AND 'cease date is blank', hence subtracting the three cities that have been ceased regardless of the date range in the slicer. 

For example here, that should return 11 (12 that have been launched until Jan, 1 2024 minus NYC).

Thanks,
Al

[Anonymous · Accepted solution]

Hi @Al_Padrini ,

Please try this:

Measure = 
VAR __max_date = MAXX(ALLSELECTED('Date'[Date]),'Date'[Date])
VAR __result = COUNTROWS('Table') - CALCULATE(COUNTROWS('Table'),'Table'[Cease date]<=__max_date && 'Table'[Cease date]<> BLANK())
RETURN
__result

Output:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

[Al_Padrini]

Amazing, it worked. Thank you!