CALCULATE by date filter returns blank values?

[Anonymous]

Hi everyone

Apologies if this is a basic question but I have searched on this community page and I cannot find an answer.

I am attempting to produce a calculate measure that will summarise by date. At present each row has only 1 of two possible dates in it, being a table of week end data that has only been running for two weeks so far, but will increase in time. I have a date column, with the date type as DATE, in the dd/mm/yyyy format.

My measure is CALCULATE(sum(table[sales], FILTER (table, table[week ending]=dd/mm/yyyy))

At present this just returns a blank result - in both a card and in a table .

I want to be able to filter by a specific date for now - i note that if i insert a week number column and filter by that week then i get the correct result, but for now I want to know why it won't return any values if I try to filter by the date as given in the table.

thank you all in advance

[edhans]

You do not need CALCULATE for this. use SUMX with a FILTER, and the date format has to be correct:

Sales by Date =
SUMX(
    FILTER(
        'Table',
        'Table'[Week Ending]
            = DATE( YYYY, MM, DD)
    ),
    'Table'[Sales]
)

Note that CALCULATE will work, but it does something called context transition, which is expensive and can have unexpected results. Not in your example most likely, but it is best to use CALCULATE only when you need to invoke context transition, and for filtering, using FILTER() within an iterator (SUMX, AVERAGEX, COUNTX, etc.) is much faster.

[Anonymous]

Hi Edhans

Thank you for responding to my question - the solution you provided has worked.

I was hoping, for my own development, that you could provide some detail as to why CALCULATE does not work in this instance? I would have thought that SUMX and CALCULATE were essentially providing the same function in my example. In the mean time I will read up on context transition, as the term is unfamiliar to me.

[edhans]

I didn't say CALCULATE() wouldn't work - it will. But using CALCULATE for this is way overkill due to the way it invokes context transition. You can read about it here. Their book "The Definitive Guide to DAX" has a couple of chapters on the power of CALCULATE and at least 15-20 pages on how Context Transition works. 

So I avoid it unless I need it. For example, inside of ADDCOLUMNS when used with SUMMARIZE, you almost always, almost always, need CALCULATE to get the added columns to have the correct values.