Convert date stored as serial number to date in DAX


#1

I have few date fields in my SAS data set that are stored as serial numbers like. I am looking for a DAX formula to convert these serial numbers to DATE given that serial numbers in SAS uses 1/1/1960 as a start date rather than Microsoft start date of 1/1/1900. Here is a sample of date stored as serial numbers with DAX standard format conversion and expected date values:
Serial Number DAX Format Formula Expected Result
20426 03Dec1955 04Dec2015

20571 04Aug1956 05Aug2016

21469 11Oct1958 12Oct2018

Thank you,

Helal


#2

@Helal
Since you probably want to store these new dates it’s going to be better (and easier in my view) to use power query. And it’s really not that all difficult:

  1. Create a parameter, name it StartDate. This will be the 1/1/1960
    Parameter

  2. In your table that has the serial # you want to convert enter this as a new column:

    Date.AddDays(
    StartDate,
    Number.From ([Serial Number] )
    )

This will take the StartDate parameter and add the serial #, since that is days since 1/1/1960


#3

Nick,
Thank you so much. Worked like a charm.

Helal