topic Days between dates with nulls and blanks in DAX Commands and Tips

Days between dates with nulls and blanks

DjDon — Tue, 02 Mar 2021 22:09:40 GMT

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()

Re: Days between dates with nulls and blanks

rfigtree — Wed, 03 Mar 2021 06:42:22 GMT

your missing a bracket

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

Re: Days between dates with nulls and blanks

DjDon — Wed, 03 Mar 2021 14:10:51 GMT

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()