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.

Reply
Abreham
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
v-xjiin-msft
Solution Sage
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) ).

 

Reference: https://msdn.microsoft.com/en-us/query-bi/dax/topn-function-dax

 

Thanks,
Xi Jin.

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 

Greg_Deckler
Super User
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

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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