DAX Month to date

[usershan]

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.

[Anonymous · Accepted solution]

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

[amitchandak]

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

or

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

[Karthik12]

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