cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
SuzieKidd
Frequent Visitor

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

1 ACCEPTED SOLUTION
smpa01
Super User
Super User

@SuzieKidd 

 

 

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)

 

 

 

smpa01_0-1675790584176.png

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

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


View solution in original post

5 REPLIES 5
smpa01
Super User
Super User

@SuzieKidd 

 

 

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)

 

 

 

smpa01_0-1675790584176.png

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

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


@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.

Daryl-Lynch-Bzy
Resident Rockstar
Resident Rockstar

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

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors