- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Count Rows - If a date value is greater than a calendar date or blank
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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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] )
)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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] )
)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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] )
)
)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks 🙂
What does the "| |" do in that measure?
I've never used that before...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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] )
)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?

Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Power BI Monthly Update - February 2025
Check out the February 2025 Power BI update to learn about new features.

Subject | Author | Posted | |
---|---|---|---|
07-04-2024 10:37 PM | |||
Anonymous
| 11-30-2023 09:04 PM | ||
01-19-2024 07:55 AM | |||
12-19-2023 10:49 AM | |||
08-05-2024 08:54 AM |
User | Count |
---|---|
24 | |
12 | |
11 | |
10 | |
9 |
User | Count |
---|---|
18 | |
14 | |
13 | |
12 | |
10 |