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

How do I convert date serial to date and also handle zero values

How do I convert a date serial value to a date, but also need to handle zero value .

 

if (datevalue(Termed) = true then Datevalue(termded) else 0

 

Pleae help with the correct syntax.

 

Karen

3 REPLIES 3
MarkLaf
Solution Sage
Solution Sage

 

Old thread, but I'll post because this was the top result when I was looking for an answer to this, and I was a bit confused by the current responses.

 

First off, in the data model (DAX) to convert a serial number to date, just change the column to the date data type - no need to use functions.

 

For zero-handling, create a calculated column of date data type and use the following function:

 =IF([date serial number]=0,BLANK(),[date serial number])

 

Mixing different data types usually confuses DAX (i.e. you'll probably get an error) and should be avoided. For this reason, convert zeros to blanks, don't leave as zeros and mix with dates.

 

In the query editor (M), the basic formula snippet would be "if [date serial number] = 0 then null else Date.From([date serial number])" - the actual step would look like the following if you want to add a custom column:

= Table.AddColumn(#"Previous Step", "Corrected Dates", each if [date serial number] = 0 then null else Date.From([date serial number]))

 

Or if you want to just transform the serial column:

= Table.TransformColumns(#"Previous Step", {"date serial number", each if _ = 0 then null else Date.From(_)})

 

Eric_Zhang
Employee
Employee


@kfschaefer wrote:

How do I convert a date serial value to a date, but also need to handle zero value .

 

if (datevalue(Termed) = true then Datevalue(termded) else 0

 

Pleae help with the correct syntax.

 

Karen


 

Hi Karen, if the date serial value in your post means , for example, Jan 1, 1900 is serial number 1 and Jan 2,1900 is serial number 2 etc, you can add a column with below expression.

 

DT = IF(Sheet1[DATE SERIAL NUMBER]=0,"0",IF(Sheet1[DATE SERIAL NUMBER]>=32767,FORMAT(DATE(2000,1,Sheet1[DATE SERIAL NUMBER]-36523),"YYYY-MM-DD"),FORMAT(DATE(1900,1,Sheet1[DATE SERIAL NUMBER]),"YYYY-MM-DD")))

 

 Capture.PNG

  • Why "-36523"
    Based on my test, regarding DATE(YEAR,MONTH,DAY), the parameter DAY is a 2 bytes interger, any value bigger than 32767 would be truncated as 32767. Since the serial number has been over 40000 recent years, I am using '2000-01-01'(36524 days since 1900-01-01) as the baseline.

If you have any question, feel free to let me know.

Greg_Deckler
Super User
Super User

In DAX, the syntax is:

 

IF(true/falsetest,true statement, false statement)

 

So, something like:

 

IF(datevalue[Termed] = TRUE,DATEVALUE([termded]),0)

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.