Forum Discussion
Solved
SQL to DAX
How do I write the following SQL in DAX please DATEADD(s,-1,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))) Thank you
select DATEADD(s,-1,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,DATEFROMPARTS(2023,8,15)),0)))returns
2023-07-31 23:59:59.000You need DAX like this
Column = VAR dt = DATE(2023,8,15) VAR yr = YEAR(dt) VAR mo = MONTH(dt) VAR date2 = DATE(yr,mo,1) RETURN (date2-1)+0.99999 or Column = VAR dt = DATE(2023,8,15) VAR yr = YEAR(dt) VAR mo = MONTH(dt) VAR date2 = DATE(yr,mo,1) RETURN (date2-1)+CONVERT(TIME(23,59,59),DOUBLE)replace GETTODAY() with TODAY() in DAX
Why 0.99999 works here I have no idea,
AlexisOlson do you know why it works? Can we achieve that part through a calculation rather than hardcoding
Hi SuzieKidd - Could you please explain what you are trying to achieve with this formula?
Please note that the DAX DATEADD function does not support seconds, minutes and hours like its SQL cousin. You may consider using the add duration functionality in Power Query, or add a DAX functions that convert datetime to seconds (a bit like UNIX Timestamp).
Thank you