Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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(_)})
@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")))
If you have any question, feel free to let me know.
In DAX, the syntax is:
IF(true/falsetest,true statement, false statement)
So, something like:
IF(datevalue[Termed] = TRUE,DATEVALUE([termded]),0)
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |