Calculated Measure won't Filter in Table

[bvermeer]

First time post bear with me...

I have created a MTD function to view sales data for my company:

MTD = CALCULATE([Total Sales], FILTER(ALL('Date'[Date]),
          'Date'[Date] >= (EOMONTH(NOW(), -1) + 1) &&
          'Date'[Date] <= NOW()
))

This function is working fine, BUT when I load it into a table in order to report on this data I'm running into an issue. I need to break down all the sales in the given month by date for the whole month. Using a table and making the column my 'Date'[Date] function I thought this would break this MTD function down by date. Instead I get the following:

The 'MTD' measure is the one I just created and it's not getting filtered. The 'Total Sales' measure is the one referenced in the MTD measure and it's filtering fine. 

I am stuck and can't figure out what I'm missing. Any advice would be greatly appreciated. 

[Anonymous · Accepted solution]

It's because of the ALL() in your filter. You're telling it to always ignore any other date filters. That includes any filter that breaks it down to a single day.

MTD = CALCULATE(
	[Total Sales],
	FILTER(
		'Date',
		'Date'[Date] >= (EOMONTH(TODAY(), -1) + 1) &&
		'Date'[Date] <= TODAY()
	)
)

Also you should use TODAY() instead of NOW() when comparing to a date-only value, which your 'Date'[Date] should be. NOW() returns a datetime, which will never be equal to any date value, so <= only evaluates on <.

[bvermeer]

Thank you very much!