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
DjDon
Frequent Visitor

Days between dates with nulls and blanks

Hello all,

I have a table with three columns:  Seniority date, Hire date and data date.

 

I'd like to calculate the days/years between Seniority date and data date, however, the Seniority date column contains blanks and nulls along with actual dates.

 

If a given line contains either a blank or null for seniority date, then I want to look at Hire date and apply the same logic.  If Hire date contains a blank or null, then return a blank in the field for SenYrs. 

 

The below attempt at the code says there are no syntax errors but I get an error at runtime stating "The name 'isblank' wasn't recognized'.  Any help or ideas would be appreciated.

 

if(not(isblank[Seniority Date])) or  

  [Seniority Date]<>null then 

   ([data date]-[Seniority Date])/365.25

else 

 if(not(isblank[Hire Date])) or  

  [Hire Date]<>null then 

   ([data date]-[Hire Date])/365.25

else 

 blank()

2 REPLIES 2
DjDon
Frequent Visitor

Appreciate the response, however, I still get the same error.  I did try to rewrite the code but now am getting a "Token Then expected" error ar runtime:

 

if [Seniority Date] <> null || [Seniority Date] <> ""
then
([data date]-[Seniority Date])/365.25
else if [Hire Date] <> null || [Hire Date] <> ""
then
([data date]-[Hire Date])/365.25
else
blank()

rfigtree
Resolver III
Resolver III

your missing a bracket

 

if(not(isblank  [Seniority Date])) or  

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.

Top Solution Authors