DateAdd and DatesMTD not working

[kg_powerbi]

Hello All,

I am working on a measure that will return MTD sales for completed days in the month vs the same amount of days in the previous month.  For whatever reason if I use this formula it doesn't work as expected.

DATEADD(

DATESMTD('Date'[Date])

,-1,MONTH)

It turns the total days for the previous month.  So first I tested if

DATESMTD('Date'[Date]) worked as expected by created a DAX table with the DATESMTD formula and it worked as expected.

Then if I changed the formula to the below I get the whole month of November, not 11/1/2020 through 11/19/2020 as I thought it would work.

 

Then I found curbal's video https://www.youtube.com/watch?v=-xBYtOVyMTs&t=308s and made some changes to my formula to exclude the last date in my calendar table (today) and it works perfectly.

When I try to change this formula to include the last date, it returns the total days in November again.

Any idea what I'm doing wrong, or another way to code around this?  The reason I like the simplicity is that for completed months in the past, works like PREVIOUSMONTH formula, but for the current month, it works by comparing apples to apples (not previous completed MTD total vs current month completed days).

[kg_powerbi]

Thanks again for your help.  Any idea how to do Quarter To Date vs same time last quarter?

[Ashish_Mathur]

You are welcome.  Try the STARTOFQUARTER() and ENDOFQUARTETR() DAX functions.

[kg_powerbi]

Thanks Ashish.

Any idea why the DateADD (DATESMTD(Date[Date]),-1,MONTH) isn't working as expected?  Seems like a bug?

[Ashish_Mathur]

You are welcome.  I do not use the DATEADD() function.  Did my solution work?

[Ashish_Mathur · Accepted solution]

Hi,

To write a measure that will return MTD sales for completed days in the month vs the same amount of days in the previous month, you do not need to create another Date table.  Assuming you already have a Calendar table which only goes till the data till which there is sale, write these measures.

Total sales = sum(data[sale])

Total sales in same period last month = if(max(calendar[date])<eomonth(max(calendar[date]),0),calculate([total sales],datesbetween(calendar[date],edate(min(calendar[date]),-1),edate(max(calendar[date]),-1))),calculate([total sales],previousmonth(calendar[date])))

To your visual/filters/slicers, drag date and other other date field (month/year) from the Calendar Table.