help...understanding DAX Expression...

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.

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