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

2 Replies

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