Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Frequent Visitor

I need to convert this T/SQL in to DAX

SELECT top 10 Column1,Column2
FROM Table1
WHERE DATEPART(m, CreateDate) = DATEPART(m, DATEADD(m, -1, getdate()))
AND DATEPART(yyyy, CreateDate) = DATEPART(yyyy, DATEADD(m, -1, getdate()))

3 REPLIES 3
Solution Sage

Hi @Abreham,

Something like:

```Test =
TOPN (
10,
FILTER (
'calendar',
MONTH ( 'calendar'[Date] )
= MONTH ( TODAY () ) - 1
&& YEAR ( 'calendar'[Date] )
= YEAR ( TODAY () ) - 1
),
'calendar'[Date]
)```

Put it in a calculated table. And 'calendar' is just a calendar table: calendar = CALENDAR(DATE(2017,01,01),DATE(2018,12,31) ).

Thanks,
Xi Jin.

Frequent Visitor

Thank you for replying. It's very close but it has an issue. I have remoed TOP from T/SQL. I just need  LastMonth record but it should be Dynamic.

select Column1  from TableName

WHERE DATEPART(m, ColumnFromTable) = DATEPART(m, DATEADD(m, -1, getdate()))
AND DATEPART(yyyy, ColumnFromTable) = DATEPART(yyyy, DATEADD(m, -1, getdate()))

Thanks

Super User

The DAX equivalents are:

SELECT top 10 => TOPN

DATEPART => MONTH, YEAR, DAY

DATEADD => DATEADD

getdate => TODAY, NOW, UTCTODAY, UTCNOW

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors