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
Kristofferaabo
Helper IV
Helper IV

How to calculate days between two columns with blank cells in both columns

Hi, I want to calculate the number of days between two columns. But some of the columns are blank leaving a very large number (e.g. 42356 or -34822)

 

There can be blank cells in both columns..

I have tried a nested IF OR ISBLANK and then a diffdate, but without luck..

 

1 ACCEPTED SOLUTION

Hi @Kristofferaabo,


In both columns there are some blank cells leaving me with issues...


So how do you want to show the value of how many days or months there is between A and B when there is blank in either of the two columns?

 

If you want to show the value as blank when there is blank in either of the two columns, then you should be able to use the formula below to create the calculate column to calculate how many days there is between column A and column B.

DaysDiff = 
IF (
    ( ISBLANK ( Table1[column A] ) ) || ( ISBLANK ( Table1[column B] ) ),
    BLANK (),
    DATEDIFF ( Table1[column A], Table1[column B], DAY )
)

If that is not the case, could you be more precisely with your requirements? It's better to post some sample data with your expected results, or share a sample pbix file in your scenario.Smiley Happy

 

Regards

View solution in original post

4 REPLIES 4
parry2k
Super User
Super User

Try to make me understand how you like calculation to behave

 

Assuing you have two columns

 

Start Date

End Date

 

Calculate dates using above field, what you expect  if Start Date is blank or End Date is blank or both are blank?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Hi @parry2k I have two columns A and B. I want to know how many days or months there is between A and B.

 

In both columns there are some blank cells leaving me with issues...

 

Thanks

Kristoffer

 

Hi @Kristofferaabo,


In both columns there are some blank cells leaving me with issues...


So how do you want to show the value of how many days or months there is between A and B when there is blank in either of the two columns?

 

If you want to show the value as blank when there is blank in either of the two columns, then you should be able to use the formula below to create the calculate column to calculate how many days there is between column A and column B.

DaysDiff = 
IF (
    ( ISBLANK ( Table1[column A] ) ) || ( ISBLANK ( Table1[column B] ) ),
    BLANK (),
    DATEDIFF ( Table1[column A], Table1[column B], DAY )
)

If that is not the case, could you be more precisely with your requirements? It's better to post some sample data with your expected results, or share a sample pbix file in your scenario.Smiley Happy

 

Regards

Thanks, I was missing the couble ISBLANK! - this helped!

Kristoffer

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.