What happens when table used as a whole in calculate?

[Sasha]

Hi,

this is rather a general question to make sure I don't do logical mistakes.

What happens when you use a table as a filter in caclulate without putting filter on it or ALL etc?

Consider the following example.

I want to caclulate total sales in the last three months of the data.

To make sure it's always the same months I want I made a connection between data table and my sales table on M_Diff.

M_Diff in dates table is thedifference in months from fact table's max date. 

Next, I set a filter on the page of Dates[M_Diff] is 0,1,2 - meaning the last quarter.

I then drill through to other page where I compare between the following measures:

Total_Sales1 = 

CALCULATE(SUM(Sales[Sales]), FILTER(ALL(Sales[M_diff]), Sales[M_diff]=0 || Sales[M_diff]=1 || Sales[M_diff]=2))

Total_Sales2 = 

CALCULATE(SUM(Sales[Sales]), Dates[M_diff], FILTER(ALL(Sales[M_diff]), Sales[M_diff]=0 || Sales[M_diff]=1 || Sales[M_diff]=2))

Total_Sales3 = 

CALCULATE(SUM(Sales[Sales]), ALL(Dates[M_diff]), FILTER(ALL(Sales[M_diff]), Sales[M_diff]=0 || Sales[M_diff]=1 || Sales[M_diff]=2))

1 and 3 give the desired answer while 2 doesn't and seem to ignore all the sales in M_Diff=0 (no other filter implied).

I know the addition in 3 about ALL(Dates[M_Diff]) is not necessary, but why does 2 ignore M_Diff=0?

I did the same exercise while remaining with the filter page on 0,1,2, but changing the measures to 3,4,5(previous quarter) and all three of them showed the same result - so I'm quite clueless why.

I know time intelligence can be used, but this example is important for me to understand further in my work.

Thanks!

[AlB · Accepted solution]

Hi @Sasha 

It all comes from the Dates[M_diff] argument. What are you trying to do exactly with that? Note that you are not using a typical condition such as Dates[M_diff] = 0 but just Dates[M_diff]. Effectively, that argument will filter the table with the condition Dates[M_diff], i.e., where Dates[M_diff] is TRUE(), i.e., where Dates[M_diff] <> 0. That is why you are losing the zeros; you are filtering them out. In the case of the previous quarter (3,4,5) you are not looking at zeros and although they are being filtered out as well, you do not notice the difference.         

Total_Sales2 =
CALCULATE (
    SUM ( Sales[Sales] ),
    Dates[M_diff],
    FILTER (
        ALL ( Sales[M_diff] ),
        Sales[M_diff] = 0 || Sales[M_diff] = 1 || Sales[M_diff] = 2
    )
)

Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.

Cheers  

[Sasha]

@AlB  Thanks!

Actually, I made this formula by mistake and then its result bothered me because I felt I don't understand something crucial and indeed it was the case 🙂

Thank you!