Count Rows - If a date value is greater than a calendar date or blank

[Kmcdonald]

Hi,

I've been struggling with this issue for a bit now. Below is an excert of the data I'm trying to figure this out for.

To get the rest of the measures I need to use, I've created a table with the formula below.

The measure I'm trying to write is to count the rows where the "VOI Disposition Date (Closed)" is either blank or greater than that date in the calendar. 

So what I thought was the logic where I could use the NOT function and put count anything less than this "VOI Disposition Date (Closed)" as this should count therefore anything that is blank or a date that is greater than I think...

Below is what I wrote with no luck, as it seems to not bring the calendar date value, when I'm saying that I'm not wanting any value that is less than the value in the calendar (see how the calendar date field goes grey).

Can anyone help me to count the rows I'm after?

Thanks,

Kurt

[Kmcdonald]

@tamerj1  does the link below work?

https://1drv.ms/u/s!AmCPHJJvoRqjoVWVAvghw3qIU_Qo?e=eqrQbB 

This is with my onedrive but haven't tried sharing before...

[tamerj1 · Accepted solution]

@Kmcdonald 
Well that was a little confusing. It took me a while to figure it out. The difference in both numbers is coming from the blanks in the start date which we did not count for in our code. How would you prefer to deal with blank start date? As if it didn't start?

[Kmcdonald]

@tamerj1  thank you, you've helped me sort this and I'm really greatful for your time.

I have accounted for it now with the measure below (not counting any blank "start date" rows) and follow up with the team on why there isn't a date and how the company wants to handle this going forward.

[tamerj1]

@Kmcdonald 
Yes. Let me check the file and get back to you

[Kmcdonald]

Hi @tamerj1 ,

This is the measure I used, I changed the last bit of the measure to be "start date" (you had the closed date) as that is the condition where I want to account for having a start date of equal or less than the calendar date.

This is what the last day of each month looks like:

The 31/1/22 should be 121 though and 28/2/22 148, yet January using this measure has 152 and February 157. It is as if there is a relationship in the way but we removed the QE Date filters.

I'm also not sure why if I use table, why it is showing "152" as a total...

Is there a way I can share the file for you to see if I've made any errors in trying to put this together?

I think the logic is pretty straight forward but it could be something to do with how I set up the calendar, as the measure you suggested makes sense...

[tamerj1]

Hi @Kmcdonald 
You can upload to Wetransfer for example and share the link

[Kmcdonald]

Sorry, using the calendar table I've made I want to count on each given day how many date fields of "VOI Disposition Date (Closed)" is blank or greater than the calendar date.

[Kmcdonald]

@tamerj1 , I'm trying to figure out on any given date how many of this events were open.

So basically if it was blank on this day it is effectively open. Therefore on a later day if this was closed, so it has a date in this field now, this logic should account for that when looking back at this day.

SOrry I forgot to mention you on my first reply...

[tamerj1]

Hi @Kmcdonald 
Please try

CAPAS Open =
CALCULATE (
    COUNT ( 'CAPA Track and Trend'[CAPA ID1] ),
    ISBLANK ( 'CAPA Track and Trend EVOI'[Completion Date (CLosed)] )
        || 'CAPA Track and Trend EVOI'[Completion Date (CLosed)] >= MAX ( 'OE Date'[Date] )
)

[Kmcdonald]

@tamerj1 , does the measure I've made below look right to you?

Below is how the table worked out, which isn't exactly what I'm after but what I found interesting is the total at the bottom of the table is almost exactly how many are outstanding as of today...

What do you think I should try next?

It isn't quite right are the end of April should be showing 153 on that line.

THanks :),

Kurt

[tamerj1]

@Kmcdonald 

You can see that 105 are Shown under Blank date and this is due to the relationship. The blank dates are actually open therefore the total number would be 105+10=152

try this code if not working just delete or disable the relationship 

CAPAS Open =
CALCULATE (
    COUNT ( 'CAPA Track and Trend'[CAPA ID1] ),
    REMOVEFILTERS ( 'OE Date' ),
    ISBLANK ( 'CAPA Track and Trend EVOI'[Completion Date (CLosed)] )
        || 'CAPA Track and Trend EVOI'[Completion Date (CLosed)] >= MAX ( 'OE Date'[Date] )
)

[Kmcdonald]

@tamerj1 , thank you for that but I think I need to tweak the logic slightly. You've been able to guide me to the logic I've asked for but unfortunatley I need to add one more condition.

Can we add to the measure that it will not count rows, where the "Start Date" column is greater than the calendar date we are using?

Below is the current result, where the latest month is correct but all the ones before it will increase as time goes on and those previous months defintiely did not have that many open CAPAs. So what I'm thinking is if it counts the number of rows that area blank, where the closed date is greater than that calendar date, and not count rows where the "start date" column is greater than that calendar date.

Thank you for your help.

[tamerj1]

@Kmcdonald 

Yes sure

CAPAS Open =
CALCULATE (
    COUNT ( 'CAPA Track and Trend'[CAPA ID1] ),
    REMOVEFILTERS ( 'OE Date' ),
    ISBLANK ( 'CAPA Track and Trend EVOI'[Completion Date (CLosed)] )
        || AND (
            'CAPA Track and Trend EVOI'[Completion Date (CLosed)] >= MAX ( 'OE Date'[Date] ),
            'CAPA Track and Trend EVOI'[Start Date (CLosed)] <= MAX ( 'OE Date'[Date] )
        )
)

[Kmcdonald]

Thanks 🙂

What does the "| |" do in that measure?

I've never used that before...

[tamerj1]

@Kmcdonald 
This is the "OR" operator. 
Did it work?

[Kmcdonald]

@tamerj1 , not quite...very close though.

This is the table I have, using the loaded calendar dates.

So I thought I'd test the logic before moving forward so I looked at January and went into the data tab to see what the total should be in Jan to prove 216 is correct.

So I set the "Start Date" to be before the 1/2/22:

Then I filtered the closed date seperately as being either blank or to be equal to or after the that date and it returned 121 rows. The table is showing 216 for January though, so I'm not sure what I've done wrong here and trying to figure it out.

When blank:

When equal to and after the 31jan22:

[tamerj1]

@Kmcdonald 

You did nothing. It is jud the code logic is different than what I thought. Please use this instead 

CAPAS Open =
CALCULATE (
    COUNT ( 'CAPA Track and Trend'[CAPA ID1] ),
    REMOVEFILTERS ( 'OE Date' ),
    OR (
        ISBLANK ( 'CAPA Track and Trend EVOI'[Completion Date (CLosed)] ),
        'CAPA Track and Trend EVOI'[Completion Date (CLosed)] >= MAX ( 'OE Date'[Date] )
    ),
    'CAPA Track and Trend EVOI'[Start Date (CLosed)] <= MAX ( 'OE Date'[Date] )
)

[tamerj1]

Hi @Kmcdonald 

when you say

"The measure I'm trying to write is to count the rows where the "VOI Disposition Date (Closed)" is either blank or greater than that date in the calendar. "

which date of them?