Count if Date is > max date of filter

[NewbieJono]

i have a completion coloumn,  that holds a date if a case is completed and is blank if the case is still open.

is there a method to count

if there is a completion date and

if it is greater than the max date of the the filter selection.

[selimovd]

Hey @NewbieJono ,

yes, you can check that with measures.

For the check if it's completed you should try something like that:

Is completed =
CALCULATE(
    COUNTROWS( myTable ),
    myTable[completion column] <> ""
)

And for the check about the completion date the following measure should work:

Completed after Max Chosen Date =
CALCULATE(
    COUNTROWS( myTable ),
    myTable[completion column] >= MAX(myDateTable[Date])
)

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[NewbieJono]

i seem to be getting this error

[v-rzhou-msft · Accepted solution]

Hi @NewbieJono 

Max(datetable[Date]) is a dynamic function to return the max date you select in your slicer or filter. To use this dynamic value in your measure, you can use VAR function to get the dynamic value and then use it.

I build a sample and update the measure, you can try this way.

My Sample:

Build an unrelated Date table for filter.

Date = CALENDAR(DATE(2021,01,01),DATE(2021,07,31))

Measure:

Completed after Max Chosen Date = 
VAR _MaxFilterDate =
    MAX ( 'Date'[Date] )
VAR _IsComplete =
    CALCULATE ( COUNTROWS ( 'Sample' ), 'Sample'[Completion] <> BLANK () )+0
VAR _AfterFilterIsComplete =
    CALCULATE ( COUNTROWS ( 'Sample' ), 'Sample'[Completion] >= _MaxFilterDate )
VAR _Result =
    IF ( ISFILTERED ( 'Date'[Date] ), _AfterFilterIsComplete, _IsComplete )+0
RETURN
    _Result

Result is as below. 

By default it will show count of cases which are completed.

When I select  2021/01/19 and 2021/05/08 in Slicer it will count the cases which are completed after the max filter date (2021/05/08). 

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.