Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to Solution.
select DATEADD(s,-1,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,DATEFROMPARTS(2023,8,15)),0)))
returns
2023-07-31 23:59:59.000
You 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
select DATEADD(s,-1,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,DATEFROMPARTS(2023,8,15)),0)))
returns
2023-07-31 23:59:59.000
You 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
@smpa01 You're subtracting a day and then adding back almost a whole day.
(1-0.99999) days = 0.000001 days = 0.864 seconds.
So, essentially, you're taking midnight on the first of the month and subtracting roughly one second.
Since this is in the Power Query forum, I'd recommend
Date.StartOfMonth(DateTime.LocalNow()) - #duration(0,0,0,1)
In DAX, I'd recommend
EOMONTH ( TODAY(), -1 ) + TIME ( 23, 59, 59 )
Thank you I'll have a play with this.
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
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
157 | |
60 | |
60 | |
28 | |
19 |