MySQL to DAX conversion

[Anonymous]

Please help me convert this MYSQL query to DAX. 

select year(datereceived) as year, month(datereceived) as month,count(*)
from SampleTable
where (datereceived >= DATE_ADD(LAST_DAY(DATE_SUB(NOW(), INTERVAL 2 MONTH)), INTERVAL 1 DAY)
and datereceived < DATE_ADD(LAST_DAY(DATE_SUB(NOW(), INTERVAL 1 MONTH)), INTERVAL 1 DAY))
and statuscode in(1000, 2000, 3000, 4000, 5000)
group by year, month
order by year,month

Thanks in Advance!

[amitchandak · Accepted solution]

@Anonymous Seem like you need a month behind sales. Or last month sales, Use time intellignece for that

MTD Sales = CALCULATE(countrows(Table)]),DATESMTD('Date'[Date]),Table[statuscode] in {1000, 2000, 3000, 4000, 5000})
last MTD Sales = CALCULATE(countrows(Table)]),DATESMTD(dateadd('Date'[Date],-1,MONTH)),Table[statuscode] in {1000, 2000, 3000, 4000, 5000})
last MTD (complete) Sales =  CALCULATE(countrows(Table)]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))),Table[statuscode] in {1000, 2000, 3000, 4000, 5000})
last year MTD Sales = CALCULATE(countrows(Table)]),DATESMTD(dateadd('Date'[Date],-12,MONTH)),Table[statuscode] in {1000, 2000, 3000, 4000, 5000})
last year MTD (complete) Sales =  CALCULATE(countrows(Table)]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))),Table[statuscode] in {1000, 2000, 3000, 4000, 5000})
Month behind Sales = CALCULATE(countrows(Table)]),dateadd('Date'[Date],-1,Month),Table[statuscode] in {1000, 2000, 3000, 4000, 5000})

2nd last MTD Sales = CALCULATE(countrows(Table)]),DATESMTD(dateadd('Date'[Date],-2,MONTH)),Table[statuscode] in {1000, 2000, 3000, 4000, 5000})
2 Months behind Sales = CALCULATE(countrows(Table)]),dateadd('Date'[Date],-2,Month),Table[statuscode] in {1000, 2000, 3000, 4000, 5000})

The column of group will be used in viual as axis/row etc. And should part of your date table

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

[parry2k]

@Anonymous Unfortunately it is hard to provide a solution based on your SQL, I would recommend, paste your sample data and expected output and that is the better approach rather converting SQL to Dax, these are two different things. 

SQL statement pulls a static data, DAX is where people are interactive with visual and expected to see correct numbers as they interact with visual, which is not applicable for SQL. 

Read this post to get your answer quickly.

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