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

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!