Need help converting date functions from SQL Server to DAX

AOE_player 0
Viewed 38

I am trying to convert the following date function from t-sql to dax. My goal is to get the adjusted_date column I got from SQL into PowerBi.

select
EOMONTH(DATEADD (dd, -1, DATEADD(qq, DATEDIFF(qq, 0, [date]) +1, 0)), 1)
;

So far I have DATEDIFF as

DATEDIFF( 0, [date], QUARTER)

however I can't figure out how to do DATEADD. For that I have this:

DATEADD(0, DATEDIFF( 0, [date], QUARTER)+1, quarter)

I got this function from https://dax.guide/dateadd/

I know EOMONTH exists in DAX as show here: https://docs.microsoft.com/en-us/dax/endofmonth-function-dax

I was hoping someone can provide my a hint or help me. Thank you.

date,adjusted_date
2020-01-01,2020-04-30
2020-02-05,2020-04-30
2020-03-22,2020-04-30
2020-04-01,2020-07-31
2020-05-28,2020-07-31
2020-06-19,2020-07-31
2020-07-01,2020-10-31
2020-08-01,2020-10-31
2020-09-17,2020-10-31
2020-10-01,2021-01-31
2020-11-09,2021-01-31
2020-12-20,2021-01-31
daxstudio dax powerbi
1 Answers

TLDR: DAX

ENDOFMONTH(DATEADD(ENDOFQUARTER([date]), 1, DAY))

While we can translate expressions between the two query languages, it is important to understand the original SQL expression and be able to explain it in terms other than SQL. This will help to avoid confusion but will also help you to identify alternative native functions that will produce the same result using syntax that is more intuitive and easier to maintain.

  • Readability of your code is always important, if the intent of your code is not obvious, then future maintenance tasks may misinterpret your logic and refactor it into something else entirely.

Your initial SQL is selecting the Last Day of the First Month in the Next Quarter, but it is getting there in a convoluted way, it is going to the last day of the quarter and then finding the last day of the next month.

SQL

EOMONTH(DATEADD (dd, -1, DATEADD(qq, DATEDIFF(qq, 0, [date]) +1, 0)), 1)
-- Simplified to:
EOMONTH(DATEADD(qq, DATEDIFF(qq, 0, [date]) +1, 0))

So this can be directly transposed into DAX using the equivalent functions by swapping the arguments, in DAX for Date Functions, the interval is the last argument, in SQL it is the first.

Transact-SQL DAX
DATEDIFF (Transact-SQL) DATEDIFF (DAX Function)
DATEDIFF ( datepart , startdate , enddate ) DATEDIFF ( <Date1>, <Date2>, <Interval> )
DATEADD (Transact-SQL) DATEADD (DAX Function)
DATEADD ( datepart , number , date ) DATEADD( <Dates>, <NumberOfIntervals>, <Interval> )
EOMONTH (Transact-SQL) ENDOFMONTH (DAX Function)
EOMONTH ( start_date [, month_to_add ] ) ENDOFMONTH ( <Dates> )

DAX

ENDOFMONTH(DATEADD(0, DATEDIFF(0, [date], QUARTER) +1, QUARTER)))

But if we understand the intent, we can use different functions in DAX. Taking a hint from the convoluted SQL, we could use EOMONTH with an Offset of 1, but DAX ENDOFMONTH doesn support an offset, so we use DATEADD to add a Day to the result of the ENDOFQUARTER (DAX Function) to get the equivalent result:

ENDOFMONTH(DATEADD(ENDOFQUARTER([date]), 1, DAY))
Chris Schaller0
Related