Forum Discussion

bvermeer's avatar
bvermeer
Regular Visitor
9 years ago
Solved

Calculated Measure won't Filter in Table

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(N...
  • Anonymous's avatar
    Anonymous
    9 years ago

    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 <.

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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's avatar
bvermeer
Regular Visitor
9 years ago

Thank you very much!