1

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://learn.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
AOE_player
  • 536
  • 2
  • 11
  • SQL doesn't really convert to DAX. They're completely different paradigms. Do you have some sample data and expected output? Someone can then help. – Davide Bacci Sep 09 '22 at 18:39
  • Sorry about that @David. I have added my data in the my question. I want the second column through DAX in Powerbi. I am aware that I won't be able to convert everything into DAX, but wanted to figure out how to achieve something similar. – AOE_player Sep 09 '22 at 19:39
  • 1
    One trick is to convert datetime into a Unix Timestamp so like 2022-09-09 would turn into "1662770289" and you could do the same with any date .. and then subtract the difference. Then convert it from that time format into the type you need. Its a good way to compare date types that aren't in the same format by forcing them into one to compare first. – easleyfixed Sep 09 '22 at 19:41
  • 1
    so your function calculates the end of the month following the end of the quarter that the input date is in? I'd try ENDOFQUARTER -> DATEADD one day -> ENDOFMONTH? – 9bO3av5fw5 Sep 09 '22 at 19:41
  • Thank you @9bO3av5fw5, does this look okay? dax_adjusted_date = EOMONTH((ENDOFQUARTER(adjusted_date[date]) + 1), 1) It gave me same result but wanted to verify. – AOE_player Sep 09 '22 at 20:04
  • 1
    not sure about the mix of eomonth and endofmonth? I'm not a dax person though - just quite like date calculations ;) I'd have thought `ENDOFMONTH(ENDOFQUARTER(adjusted_date[date]) + 1)` ? – 9bO3av5fw5 Sep 09 '22 at 20:43

1 Answers1

1

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 Schaller
  • 13,704
  • 3
  • 43
  • 81