DAX evaluation of last year measure whats going on

[Anurag]

Hi,
I made a measure which calculate last year sale as below:
Ly_sale =
CALCULATE ( [Total_sales], DATEADD ( ‘Calendar’[Date], -1, YEAR ) )

To know more i make another measure which calculate ly_cumative_sale:

Ly cumative sale =
VAR LastVisibleDate =
MAX ( ‘Calendar’[Date] )
VAR FirstVisibleDate =
MIN ( ‘Calendar’[Date] )
VAR Result =
CALCULATE ( [Ly_sale], ‘Calendar’[Date] <= LastVisibleDate )
RETURN
Result

Now i am confused what is going in this measure .
[Ly_sale] is replaced in Var result with its above definition. if yes then its should override the filter of outer calculate but its giving me the right result can you tell me about what happening here .

How its giving me right result .Becuze i think it should return the Ly sale value again.

[Harsh]

Hello @Anurag,

Thank You for posting your query onto the Forum.

The outer filter of the calulate will only be over-ridden only when you reference the “Variable” just besides the CALCULATE function. Right now, what you’re referencing is the measure itself and not a variable so it’s using a measure branching technique right now.

Ly cumative sale =
VAR LastVisibleDate = MAX ( ‘Calendar’[Date] )

VAR FirstVisibleDate = MIN ( ‘Calendar’[Date] )

VAR Result =
CALCULATE ( [Ly_sale] , 
   ‘Calendar’[Date] <= LastVisibleDate )

RETURN
Result

So I would like to explain a difference with the examples of about where you went wrong in this with the help of the screenshots provided below -

1. Over-Ride Outer Filter Context of “CALCULATE” function -

So in the above screenshot you’ll observe that when you reference the variable just besides the CALCULATE function it’ll simply over-ride the outer filter context and will provide you the same results.

2. Consider the Outer Filter Context of “CALCULATE” function -

And now, if you observe the above the screenshot, you’ll see that in order to consider the outer filter context while using the “VARIABLES” technique I’ve used “CALCULATE” function right at the start of the formula so that it evaluates the results correctly rather than providing the same results as we witnessed in the previous screenshot.

I’m also attaching the working of the PBIX file for the reference so that you can go through it. Also I’m providing a link of the article as well as of a video below which will further clarify your concept/take on this.

Hoping you find this useful and helps you to understand the conecpt more clearly.

Thanks and Warm Regards,
Harsh

CALCULATE Context - Harsh.pbix (661.3 KB)

Caution When Using Variables in DAX and Power BI

Caution When Using Variables in DAX and Power BI

[Rajesh]

@Anurag

I didn’t get how you are getting correct values for LY Cumative sales.

Could you please explain ?

@Harsh

You explained what happens when we are creating VAR outside calculate function and inside calculate function.

When we are creating VAR outside calculate, VAR doesn’t effect with Filter context coming from Calculate, that’s we are seeing same numbers.

When we are creating VAR inside calculate, first it applies the filter context and then it evaluates expression in first argument

But here its different.

Here @Anurag using measure inside calculate.

CALCULATE ( [Ly_sale], ‘Calendar’[Date] <= LastVisibleDate )

CALCULATE ( CALCULATE ( [Total_sales], DATEADD ( ‘Calendar’[Date], -1, YEAR ) ), ‘Calendar’[Date] <= LastVisibleDate )

Here the filter context is on Calendar Date in both the calculate, but its not override

Please let me know if I’m Wrong

[Rajesh]

@Harsh, @Melissa, @BrianJ

Could you please test this ? Why the measure not considering year filter coming from Table.

Calculate.pbix (275.9 KB)

[Harsh]

@Anurag

[Ly_sale] is replaced in Var result with its above definition. if yes then its should override the filter of outer calculate but its giving me the right result can you tell me about what happening here .

Hello @Rajesh,

As you’ve suggested, “Here the filter context is on “Calendar Date” in both the calculate, but its not override” - Nope. The query was regarding as per the above quoted statement - about why “[Ly_sale]” is giving correct results when placed with the VAR. For this, the answer is already provided above.

And another query which you’ve asked that is -

I didn’t get how you are getting correct values for LY Cumative sales.

Could you please explain ?

So when I created the measure as per the conditions provided by @Anurag then yes, it will give you the correct results. Below is the screenshot of the result provided for the reference -

The concept which @Anurag had applied is almost similar to as per the video link provided below where one can use the variables under the “filter” part of the CALCULATE function

Also if you’re thinking/wondering that how it derived the correct results without using FILTER( ALLSELECTED( Dates )? Then the answer is - It’s not mandatory in this case to create a filtered table of Dates and then within that mention this condition - Dates[Dates] <= LastVisibleDate because the syntax of the CALCULATE function by default involves “FILTER condition” so whether you use FILTER function or not it doesn’t matter in this case. Below is the screenshot provided for the reference of both the results and you’ll observe that both the “Cumulative Sales” measure yields the same results irrespective of use of FILTER function.

The only important thing here to be noted is that one needs to understand when to include/exclude the FILTER condition otherwise it’ll lead to ambiguous results. And that why it’s always “suggested” to use it in order to prevent from deriving the ambigious results but not at all “mandatory” which @Anurag has used in this case .

Lastly, I’m also attaching the working of the PBIX file for the reference.

Hoping you find this useful.

Thanks and Warm Regards,
Harsh

CALCULATE Context - Harsh.pbix (662.3 KB)

How To Use Variables in DAX Formula with Power BI

[Harsh]

Hello @Rajesh,

Are you trying to calculate “Cumulative Last Year” results in the give file? If Yes, then you might have referenced the wrong formula to calculate “Cumulative Last Year” because in the given case although it’s cumulating the results but it’s doing from Year - 2015. Just change the fomula as provided below and it’ll provide the correct cumulative results -

Thanks and Warm Regards,
Harsh

[Rajesh]

@Harsh Thanks for your immediate response

Sorry to bother you.

My Question is why the measure not consider Year filter coming from Table.
It should give the same result as Total Sales right ?

Year and Date both are different fields right why it is overwrite

[Melissa]

Hi @Rajesh,

No this predicate DateTable[Date] <= LastVisibleDate is internally translated to:

FILTER( ALL( DateTable[Date] ),
     DateTable[Date] <= LastVisibleDate
)

[Harsh]

Hello @Rajesh,

Had your formula been something like this as provided below then it would have derived the same Sales amount as you’re expecting.

But since you’re referencing the measure under the “Expression” argument of the “FILTER” function it’s considering as a measure branching approach and lastly as mentioned by @Melissa it’s predicating the DateTable[Date] <= LastVisibleDate as -

FILTER( ALL( DateTable[Date] ),
     DateTable[Date] <= LastVisibleDate )

Which I’ve been trying to say in the last post. Because in this case, it’s not required to use FILTER function explicity under the FILTER argument but it’s just an generally accepted principle to use it under the FILTER argument to avoid any ambigious results.

In layman’s language if I put it - It’s just an habit which we’ve developed to use it.

Attaching the PBIX file for the reference.

Thanks and Warm Regards,
Harsh

Calculate - Harsh.pbix (276.4 KB)

[Rajesh]

@Harsh Thanks for helping me.

Let me explain clearly my problem.

I’m not using any measure now.

Result1 - It should not give cumulative sales because there is a year filter coming from Table.
It should consider both year filter coming from Table and Filter argument from Calculate
both are different fields, but it is overwrites why ?

Result2 - To Ignore the year filter coming from table we can use ALL…This is Fine

Result3 - This is equal to Result 1… but both are giving different results.

Result4 - This is equal to Result 2 … This is Fine

Calculate.pbix (277.4 KB)

Once again Thanks for Helping

[Melissa]

@Rajesh

Result1 - It should not give cumulative sales because there is a year filter coming from Table.
It should consider both year filter coming from Table and Filter argument from Calculate
both are different fields, but it is overwrites why ?

Again that filter context coming from the Year is removed by the predicate which is translated to:
FILTER( ALL( 'Date'[Date] ), 'Date'[Date] <= LastVisibleDate )
if you want that external filter context back use KEEPFILTERS

For the DAX engine the expressions Result1 and Result1Full are exactly the same.

play around with this.

Count Dates =
VAR LastVisibleDate = MAX( 'Date'[Date])
VAR Result1 = CALCULATE( COUNTROWS( 'Date'), 'Date'[Date] <= LastVisibleDate)
VAR Result1Full = CALCULATE( COUNTROWS( 'Date'), FILTER( ALL( 'Date'[Date] ), 'Date'[Date] <= LastVisibleDate ))
VAR Result1B = CALCULATE( COUNTROWS( 'Date'), 'Date'[Date] <= LastVisibleDate, KEEPFILTERS('Date'))
VAR Result2 = CALCULATE( COUNTROWS( 'Date'), 'Date'[Date] <= LastVisibleDate, ALL('Date'[Year] ))
VAR Result3 = CALCULATE( COUNTROWS( 'Date'), FILTER( 'Date','Date'[Date] <= LastVisibleDate ))
VAR Result4 = CALCULATE( COUNTROWS( 'Date'), FILTER( ALL('Date'),'Date'[Date] <= LastVisibleDate ))
RETURN

Result1

[Rajesh]

Thanks @Melissa and @Harsh

I really don’t know about this form of a predicate with a single column reference.

[Melissa]

Seems you’ve got it now @Rajesh
Good one

[Harsh]

Hello @Rajesh,

That’s really good that you came to know about how the expression and argument actually works under the CALCULATE function.

Lastly, before we sign off from this thread. I would just like to shed some light about how “Result1” is different from the “Result3”.

When we reference the naked table under the FILTER function it firstly generate the whole table virtually. Now, since we’ve haven’t wrapped that table under either under the following function “ALL”, “ALLSELECTED” or “ALLEXCEPT”. It won’t able to recognize which function to adopt by default out of these three. And since it’s not able to categorize which one to use it’ll simply discard the FILTER function as good as making it null and void.

So although our formula may look like this -

VAR Result3 = 
CALCULATE( SUM( Sales_Data[Sales] ) ,
    FILTER( DateTable , 
        DateTable[Date] <= LastVisibleDate ) )

But in actual it’ll be working like this -

VAR Result3 = 
CALCULATE( SUM( Sales_Data[Sales] ) )

And that’s why it provides the same result i.e. “Total Sales = Result3”

Had we not used the FILTER( DateTable ) and kept the formula as mentioned for VAR Result1 -

VAR Result1 = 
CALCULATE( SUM( Sales_Data[Sales] ) , 
    DateTable[Date] <= LastVisibleDate )

Then in that case although we haven’t specified any condition it will by default use ALL and since DateTable[Date] <= LastVisibleDate condition is mentioned under the FILTER argument it’s as good as mentioned under the FILTER function.

So at back end the formula will actually run like the one provided below -

VAR Result4 = 
CALCULATE( SUM( Sales_Data[Sales] ) , 
    FILTER( ALL( DateTable ) , 
        DateTable[Date] <= LastVisibleDate ) )

Hope now everything is sorted on this thread.

Thanks and Warm Regards,
Harsh

[AntrikshSharma]

@Rajesh

I didn’t get how you are getting correct values for LY Cumative sales.

@Rajesh The outer filter context create by Dates[Date] <= LastVisibleDate creates a list of dates, and in this filter context the inner CALCULATE evaluates DATEADD and then shift these dates by 1 year.

@Rajesh

both are different fields, but it is overwrites why ?

When the relationship between 2 tables is based on dates data type and if dates are getting filtered then engine internally adds REMOVFILTERS to your code.

When the relationship is based on Integer Key this doesn’t happen unless you mark the date table as a date table

If you mark it as date table :

If you mark it as date table, then you need to restore filter over Year by using VALUES ( Dates[Year] ) and not with KEEPFILTERS

Calculate.pbix (321.6 KB)

[Rajesh]

@AntrikshSharma

Excellent

So it depends on the relationship fields. Could you please provide the documentation link on this ?

If possible please create a separate topic on this so that helps others to understand how the DAX engine works.

[Rajesh]

@AntrikshSharma

I found article related to this in SQLBI. I’m sharing the link

Mark as Date table - SQLBI