topic Re: DAX Month to date in DAX Commands and Tips

DAX Month to date

usershan — Wed, 15 Dec 2021 02:26:57 GMT

Hi All,

I have a formula in Oracle BI that needs to be converted to Power BI Dax measure.

Oracle BI - ToDate(SUM(table.column1), [Level Fiscal Month])

Can you all help me in this? How to write this in Power BI? I tried using calculate and DatesMTD, it is not working.

Re: DAX Month to date

amitchandak — Wed, 15 Dec 2021 03:14:35 GMT

usershan , if you need MTD, Datesmtd with date table should help

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA

Re: DAX Month to date

Anonymous — Fri, 17 Dec 2021 02:37:02 GMT

Hi usershan

If you want to calculate the data of the current month up to the current maximum date, you can use this formula.

DATESMTD : Returns a table that contains a column of the dates for the month to date, in the current context.

MTD = CALCULATE(SUM('Table'[value]),DATESMTD('Table'[Date]))

If you are not familiar with DATESMTD, DATESQTD, DATESYTD, you can refer to my pbix file, I listed different DAX applications scenarios in it . Hope it will help you !

Best Regard

Community Support Team _ Ailsa Tao

Re: DAX Month to date

Karthik12 — Wed, 11 Jan 2023 12:42:28 GMT

amitchandak Hi, Is there way to create a measure from start date to till date of the month. For example, I have Plan sheet which has day wise plan numbers till end of this month (From 01-01-2023 to 31-01-2023) but I want to know the sum of plan qty till today. Is it possible?