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 d...
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    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

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

    • Al_Padrini's avatar
      Al_Padrini
      Frequent Visitor
      2 years ago

      Amazing, it worked. Thank you!