help...understanding DAX Expression...

[Raj007]

Hi All, Thanks for your time, i am trying to understand and please excuse me if this is something very basic

..using the fact internetsales table that comes as Adventuresworks DB..

1)Created Date table using the MIN and MAX YEAR of ORder date in FactInternetSales Table

dDate = CALENDAR(DATE(YEAR(MIN(FactInternetSales[OrderDate]) ),1,1), DATE(YEAR(MAX(FactInternetSales[OrderDate])),12,31) )

2).Created a measure for sales amount

vSalesAmount = sum(FactInternetSales[SalesAmount])

trying to create a new measure for the prior year sales amount using the DATEADD function ..but what i notice something is going on when using the FILTER within the  CALCULATE and when not using the FILTER

when using FILTER in Calculate

vSalesPreviousYear = CALCULATE( [vSalesAmount],FILTER(dDate,DATEADD(dDate[Date],-1,YEAR)))

this is what i see in matrix when i use the FILTER in CALCULATE

when i dont use the FILTER within the CALCULATE i see the following matrix.. calculate FILTER is causing the difference in results

vSalesPreviousYear = CALCULATE( [vSalesAmount],DATEADD(dDate[Date],-1,YEAR))  - these numbers are accurate but i dont understand and its very confusing.. could some please help me understand it

[v-shex-msft]

HI @Raj007,

Did these suggestions help with your scenario? if that is the case, you can consider Kudo or accept the helpful suggestions to help others who faced similar requirements and find it more quickly.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[daxer-almighty]

@Raj007 

OK. Before I tell you exactly why you see what you see, let me first debunk some myths that @Greg_Deckler has been promoting on this forum.

1. CALCULATE without a filter includes an implicit ALL statement which is likey why you are seeing a difference.

> Of course, this is totally false. No such ALL exists in CALCULATE without a filter. And therefore it's not the reason why you see the difference. I'm going to demonstrate this momentarily.

2. CALCULATE and the Time "Intelligence" functions are black boxes that have dozens upon dozens of blog articles written about their nuances.

> Not sure about the "dozens upon dozens" bit and all their "nuances." I know that they have a very clear interface and their usages are clearly described in dax.guide (and the MS documentation). If you stick to the rules of the game, they never act up. You can always precisely predict their behavior and explain it.

3. I find it best to avoid them quite honestly because it is difficult to predict their behavior in complex circumstances. 

> This is just a personal opinion, of course, to which everyone has a right. I have been using them for about... 6 years and can honestly say they've never acted up on me once.

With these preliminaries out of the way, let me explain the results you're getting.

First of all, one has to remember always and forever that DAX is a very logical language and always does what it's told to. If you go by the rules (this is the most difficult part for most people), if you know them well, you'll never go astray. But you have to have a very solid grouding in logic and the DAX rules of the game.

OK. Let's dissect your first measure.

vSalesPreviousYear =
CALCULATE(
    [vSalesAmount],
    FILTER(
        dDate,
        DATEADD(
            dDate[Date],
            -1,
            YEAR
        )
    )
)

What does it do? Well, first of all, CALCULATE must evaluate the arguments in the current context starting with the 2nd one (FILTER). FILTER takes the dDate table as it's seen in the current context and then iterates over its rows evaluating its second argument (DATEADD) as a boolean expression in the current ROW context. This is FILTER's API (https://dax.guide/filter). The key to understanding what's going on is "a boolean expression" and the API of DATEADD. From dax.guide/dateadd:

The first point applies here. It means that DATEADD( 'dDate'[Date],...) is in fact replaced by DATEADD( CALCULATETABLE( DISTINCT( 'dDate'[Date] ) ), ...). And this in turn means that you are moving back in time just one date, the one that's currently being iterated. So, if you're in the second year of your calendar, you'll get the same date one year back. This will be a non-blank scalar, so it'll be converted into TRUE. Hence, your date will survive the filter. In fact all the dates will, if they're not in the first year. So FILTER will return all the dates from the current context and this is why you see the figures are the same as the current amount for all years starting with the 2nd. For the 1st year you see BLANK because such a date moved back 1 year yields BLANK. Clear?

Your second measure

vSalesPreviousYear =
CALCULATE(
    [vSalesAmount],
    DATEADD(
        dDate[Date],
        -1,
        YEAR
    )
)

does what you expect because this time you're using DATEADD not in a FILTER where you've got a row context, but in the original filter context. So, all visible dates are moved back 1 year and then these dates are returned from DATEADD as a table. This table of values/dates is then applied to the model by CALCULATE overwriting any context that  has existed on the dDate table so far and the calculation of [vSalesAmount] takes place in this new context. Of course, DATEADD( CALCULATETABLE( DISTINCT( 'dDate'[Date] ) ), ...) still takes place but since there's no row context, CALCULATETABLE has no effect whatsoever.

So, as you see, everything is so logical that it just hurts. Nothing of the stuff that @Greg_Deckler tries to make you believe.

[Greg_Deckler]

@daxer-almighty Regarding the implicit ALL, I was just reiterating the way Marco Russo explained it to me when I had a strange DAX case:

Re: DAX Head Scratcher - Microsoft Power BI Community

[daxer-almighty]

@Greg_Deckler 

I've read this conversation but... it's off topic here.

"CALCULATE without a filter includes an implicit ALL statement which is likey why you are seeing a difference."

OK. You've got it all mixed up. The "implicit ALL" you're talking about is what happens when you write code like:

calculate(
    [measure],
    T[Col] = Value
)

which always gets translated into:

calculate(
    [measure],
    filter(
        all( T[Col] ),
        T[Col] = Value
    )
)

That's because filters in CALCULATE must always be tables, not boolean expressions (in contrast to FILTER's second argument). So this shortcut, or syntactic sugar, has been added to the language in order to not force people into constantly writing the longer version and because it's so common to want to filter like this.

So, yes, there exists a sense in which you can say "CALCULATE without a filter includes an implicit ALL statement" but this statement is highly imprecise and misleading, especially in view of the fact that arguments to CALCULATE are always tables and CALCULATE with only the first argument is a perfectly valid statement, which by the way does not have a filter and does not include any (hidden) ALL statement at the same time.

But the "ALL mystery" does not even apply in this case since there's no CALCULATE here that would contain the syntactic sugar I've explained above.

[Raj007]

Thanks a lot Daxer and Greg... I need some time to understand it as it is not a simple topic.. 
I really appreciate both of your time. 

[Greg_Deckler]

@daxer-almighty So, what you're saying is that CALCULATE can have an implicit ALL. 

Quoting:

"Of course, this is totally false. No such ALL exists in CALCULATE without a filter. And therefore it's not the reason why you see the difference. I'm going to demonstrate this momentarily."

Or are we editing that post too? 😄

[daxer-almighty]

Eh... I'm really tired, Greg. Really. One last time. I'll pass the accusation of editing (and removing) stuff over in silence. I'll get straight to the point.

"Of course, this is totally false. No such ALL exists in CALCULATE without a filter."

Still, the above holds true. CALCULATE without a filter is this form of CALCULATE:

CALCULATE ( EXPRESSION/MEASURE )

This, my friend, is CALCULATE without a filter.

Any other form is CALCULATE WITH A FILTER. Even this form in boolean disguise

CALCULATE(
    EXPRESSION/MEASURE,
    T[Col] = Value
)

is CALUCLATE WITH A FILTER. Why? Because from the 2nd argument on all of them must be filters regardless of the form they take. Under the hood they are always turned into the equivalent table-filter version.

Therefore, your claim is false, while mine still is true. There never is any implicit ALL inside CALCULATE which does not have a filter. And please let in sink in. If you still don't believe what I say, feel free to ask Marco or Alberto. That should setttle the dispute for you.

Have you understood yet? Or is it still a mistery for you? I want to believe you have.

[daxer-almighty]

@Greg_Deckler 

Mate, do not try to downplay my words. You've seen the explanation, so I'm not gonna dwell upon it. Don't have time to enter into childlish discussions. Where did I edit my post, by the way?

Try to be precise, then maybe there won't be a reason to clear up the fog. And I can do it only because I happen to have some pretty advanced knowledge of DAX. Have you thought about others that do not? Do you treat them with respect by putting obscure statements like this in your answers?

Let me quote Friedrich Nietzsche here : "There are horrible people who, instead of solving a problem, tangle it up and make it harder to solve for anyone who wants to deal with it. Whoever does not know how to hit the nail on the head should be asked not to hit it at all."

[Greg_Deckler]

@daxer-almighty Just trying to understand the truth of whether or not CALCULATE can have an implicit ALL. Sounds like now we agree that it can versus prior it sounded like you disagreed with that statement and called the notion "nonsense". I don't think it's nonsense and now seems like neither do you.

[Greg_Deckler · Accepted solution]

@Raj007 CALCULATE without a filter includes an implicit ALL statement which is likey why you are seeing a difference. CALCULATE and the Time "Intelligence" functions are black boxes that have dozens upon dozens of blog articles written about their nuances. I find it best to avoid them quite honestly because it is difficult to predict their behavior in complex circumstances. 

CALCUHATE - Why I Don't Use DAX's CALCULATE Functi... - Microsoft Power BI Community

You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...