- 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
Date Difference with blank values
Hi,
I'm trying to calculate the number of days between two dates and for one of these columns, there may be blanks which I want to ignore (rather than give a large -ve value).
What DAX do I need to take this into account? I want the table to still show the entries with the blank fields, just not to add a date in the date difference calculated column (Days to close column in picture below) - thanks
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi ManNVSM,
Thanks for your help. You gave me the idea of using dummy data so I did this and came up with this formula:
Days to close = if('Breach Register'[Breach closed date]=BLANK(),'Breach Register'[Breach closed date],1.0*'Breach Register'[Breach closed date]-'Breach Register'[Date that breach discovered])
This seems to work ok.
Thanks for your help again.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Dear @Bimster,
You need to combine your DateDiff DAX with IF.
Ex: New Day to close = CALCULATE( IF ( CLoseDate <> blank(), [your DateDif Formular] , ""))
Regards,
ManNVSM.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi ManNVSM,
I just tried this and am getting the above error:. Any idea on how to resolve this. The underlying data is being pulled through from a SharePoint list.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Dear @Bimster
Try this again with ALLEXCEPT.
Ex: New day to close = CALCULATE(IF('Breach Register'[Breach closed date] <> BLANK(),DATEDIFF('Breach Register'[Date that breach discovered], 'Breach Register'[Breach closed date], day)
, ALLEXCEPT ('Breach Register'[Title]))
Regards,
ManNVSM.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks for the help. I've tried this with the allexcept addition and the same error message is appearing.
It seems to be very complicated to do a date difference whilst ignoring blanks.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Dear @Bimster,
Can you share your pbix (delete the sensitive data or create dummy data) please?
Regards,
ManNVSM.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi ManNVSM,
Thanks for your help. You gave me the idea of using dummy data so I did this and came up with this formula:
Days to close = if('Breach Register'[Breach closed date]=BLANK(),'Breach Register'[Breach closed date],1.0*'Breach Register'[Breach closed date]-'Breach Register'[Date that breach discovered])
This seems to work ok.
Thanks for your help again.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hello, Will this work in Direct Query?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Dear @Bimster,
It's nice to help you. So choose a reply that you think it 's a solution, it'll help the other user 🙂
Regards,
ManNVSM.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
I am experiencing this same issue, but this does not work for me. It removes the columns from the variance when the date is missing.
Date Variance = IF('Project'[ProjectBaseline1FinishDate]=BLANK(),'Project'[ProjectBaseline1FinishDate],1.0*'Project'[ProjectBaseline1FinishDate] -('Project'[ProjectBaseline0FinishDate]))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Could do something like:
Days To Close = if(isblank([breach closed date]),blank(),[current measure you're using that's giving -43k something])
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
The Days to close column is the one I have added as a calculated column to try and calculate the 'days to close' each breach. This used the following:
Days to close = 1.0*('Breach Register'[Date that breach discovered]-'Breach Register'[Breach closed date])
So I don't think your suggestion will work.
I did try datediff but it errored stating one date can;t be before another. This occurred whether it was x-y or y-x.
I am trying to add a column in the visual to show that where a breach has been closed (breach closed date), the time it took to close. If if hasn't been closed i.e. Breach closed date is blank, then the entry in the new column will also be blank.
Thanks.

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 | ||
06-04-2024 09:06 AM | |||
05-17-2022 12:14 AM | |||
03-06-2024 03:24 AM |
User | Count |
---|---|
87 | |
81 | |
53 | |
38 | |
35 |