Forum Discussion

Al_Padrini's avatar
Al_Padrini
Frequent Visitor
2 years ago
Solved

Count between two dates in slicer

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 nameLaunch dateCease date
(if applicable)
Paris1/06/2021 
Dallas15/09/2021 
Amsterdam4/12/2021 
Tokyo Narita13/05/20225/01/2024
Bogota5/06/2022 
New York City17/08/202224/03/2023
Rome29/08/2022 
Lagos9/12/2022 
Shanghai30/12/202217/02/2024
Frankfurt20/01/2023 
Madrid7/07/2023 
Amsterdam16/08/2023 
Jakarta24/03/2024 
Beijing6/06/2024 
Istanbul12/06/2024 
Singapore7/08/2024 
Mexico City21/08/2024 
Sao Paulo28/08/2024 
Dubai21/09/2024 
Bangkok28/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

Need Help
Tips and Tricks

4 Replies

  • Idrissshatila's avatar
    Idrissshatila
    Super User
    2 years ago

    Hello Al_Padrini ,

     

    use the following measure 

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

     

     

    • Al_Padrini's avatar
      Al_Padrini
      Frequent Visitor
      2 years ago

      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's avatar
        Anonymous
        Not applicable
        2 years ago

        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