The Power BI DATESMTD function is one of the DAX Date and Time functions useful for returning a table that contains continuous dates for the month to date. This article explains the POWER BI DAX DATESMTD function, which accepts the date value and uses the current context to return a column of dates for the month to date. The syntax is shown below.
DATESMTD(<dates>)
For the DATESMTD function, you can use the column that contains the date/time value. Otherwise, use the table expression that returns a single column as the parameter.
Power BI DAX DATESMTD function Example
To demonstrate the DAX DATESMTD() function, click the New Table button on the Modeling Tab and rename it Table. The expression below uses the FactInternetSales table OrderDate column to create a table with dates for the month.
The formula returns a table with dates for January 2014 from 01 to 28, as it is the last date in the OrderDate column. Please refer to the DAX DATESQTD and DAX DATESYTD functions.
Table = DATESMTD(FactInternetSales[OrderDate])
The expression below creates a simple measure. It will calculate the sum of the Sales Amount in the FactInternetSales table for the month to date. It means it will only work for a single month and restart every month. Please refer to the list of DAX Date functions and the all DAX functions article available on the Power BI tutorial page for the remaining methods.
If we add the below expression to the table it will calculate the running total for each month of the Order date and restart on the 1st day of each month. Please check the 4th record in the table report below. For the remaining charts, visit the Power BI Charts article.
RunSalesMTD = CALCULATE(SUM(FactInternetSales[SalesAmount]), DATESMTD(CalendarTable[Date]))
