Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
NewbieJono
Post Patron
Post Patron

Count if Date is > max date of filter

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.

 

 

1 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:

1.png

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.

2.png

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). 

3.png

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. 

View solution in original post

3 REPLIES 3
selimovd
Super User
Super User

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
 

NewbieJono_0-1625581067438.png

i seem to be getting this error

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:

1.png

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.

2.png

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). 

3.png

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. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.