Using a date in a CALCULATE filter results in incorrect totals when that date is in a table visual

[Anonymous]

I have a Measure totaling sales within a given month. It looks something like this:

Sales Dollars  = CALCULATE(SUM(Sales[Dollars]), EOMONTH(Sales[SaleDate],0) = EOMONTH(TODAY(),0))

I should note that I could have used the MONTH/YEAR functions to compare SaleDate to TODAY, or FORMAT(date, "yyyy-mm") to do the comparison. No matter what method I use the results are the same:

When I put some random Dimension (like Customer Group or Product Group) in a table and add the Sales Dollars Measure, the total are accurate. I can view any Dimension and its Sales Dollars for the current month. I can use slicers and page filters and everything works.....

Until I put "SaleDate" in that same table. When I add SaleDate to the table, it defaults to the "Date Hierarchy" display mode, which breaks that date out into its components (Year, Month, Day, Qtr, etc). This works fine, but I don't want that view, so I click on the SaleDate in the Visual settings and change it to just display the SaleDate itself (i.e. yyyy-mm-dd) and all of a sudden my totals are completely inaccute. They're vastly inflated based what appears to be the sales dollar multiplied by however many times that Dimension has records that fall within the current month.

What's weirder is that if I add the lowest level Dimension in my data to that same table (order/line), the problem with Commit Date disappears, no matter how I display it. It's as if by displaying Commit Date at an aggregate level breaks the Measure, but literally any other scenario (higher level or lower level) isn't an issue.

Can someone explain to me what's causing this, but more importantly how to work around it? I don't want my dates displayed as Hierarchies. I just want a simple date displayed, but I can't do that without risking breaking my Measure.

[amitchandak · Accepted solution]

@Anonymous , Try like

Sales Dollars = CALCULATE(SUM(Sales[Dollars]), Filter(Sales,  EOMONTH(Sales[SaleDate],0) = EOMONTH(TODAY(),0)))

if this works, refer

http://dataap.org/blog/2019/04/22/difference-between-calculate-with-and-without-filter-expression/

[Anonymous]

So, the moral of the story is never, ever use the "filter" parameters of CALCULATE, just use the FILTER function within a CALCULATE. Man, that's a dangerous pitfall to just throw in people's way.

Anyway, thanks for the clarification. Switching to using FILTER() got rid of this behavior.