Power BI - SQL to DAX?

Question

I have an rdat field that I have to convert to a date field which I know how to do in sql but am new to DAX and I'm not sure how to do this

CONVERT(CHAR(10), DATEADD(d, RDAT_ENTERED +5843, 0), 1)

in Power BI. I can fix on the sql side so that it just brings in the data but I would still like to know how this would look in BI.

Answer 1

It seems your data uses the SQL SERVER EPOCH to represent dates so RDAT_ENTERED + 5843 means a determinated number of days from 1/1/1900. You can use that information to convert it to human dates via DAX or Power Query.

Create a calculated column either in Dax or Power Query (informally known as "M")

DAX in Power BI and Power Pivot

HumanDate = DATE(1900,1,1) + [RDAT_ENTERED] + 5843

M language in Power Query

=Date.AddDays(DateTime.FromText("1900-01-01"), [RDAT_ENTERED] + 5843)

Answer 2

I realize the context of your question and recognize the excellent answer by @alehandro. However, a few suggested best practices:

• SQL is a BI tool
• Transformations to standard data types should be at as low a level as possible
• Magic numbers should not be used DECLARE @EPOCH_RDAT DATE = { d '1916-01-01' };
• Function parameters should be used as intended: SELECT DATEADD(DAY, RDAT_ENTERED, @EPOCH_RDAT);