Calculate not working with sameperiodlastyear and filter

DAX DAX Calculations
1

Hi All,
I am working out a particular Brand sales = “Bolt” , I have written 2 measures as below. The first one with the variable does not work but when I use a nested Calculate it works. Can someone explain why the variable one does not work, I’m assuming the variable used is out of scope and should be within the Calculate but even tried that it does not work but the nested calculate with the filter works.

  1. Bolt LY =
    var lysales = CALCULATE([Total Amount],SAMEPERIODLASTYEAR(‘Calendar’[Date]))
    return
    CALCULATE(lysales,FILTER(EPOS,EPOS[Brand Name]=“Bolt”))

  2. CALCULATE(
    CALCULATE([Total Amount],FILTER(EPOS,EPOS[Brand Name] =“Bolt”)),
    SAMEPERIODLASTYEAR(‘Calendar’[Date]))

Thank you,
Mustafa

2

Hi @chris786,

DAX variables are constants…
@BrianJ did a video on this subject, you can find that here.

3

Thanks Melissa once again. So in this case the Nested Calculate would do the job.

4

Well just using measure branching, is all it takes, really.

I hope this is helpful

5

@chris786 Your first measure doesn’t work because SAMEPERIODLASTYEAR shifts dates in previous year but FILTER ( EPOS… has dates of the current year and because of the concept of Expanded Tables EPOS filters the rows of the Dates table as well, thus returning a blank since the Dates or Previous Year are merged with the dates of current year.

The second mesaure works because in nested CALCULATE the order of evaluation is Outer to Inner, so outer CALCULATE prepares a filter of previous year for the inner CALCULATE and in that filter context inner CALCULATE is evaluated.

Both code are not recommended if you don’t understand how table expansion works, therefore always use 1, 2, 3 or more COLUMNS and not a table filter in DAX.

6

Thanks Antriksh, that’s a fantastic explanation, I will definitely try to understand how expanded tables work.
One question, how would you solve this using DAX with or without variables.

7

@chris786

m =
CALCULATE (
    [Total Amount],
    EPOS[Brand Name] = "Bolt",
    SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
)
8

Hi, Antriksh,

I tried the same formula earlier except that I used Filter, please see below:
CALCULATE([Total Amount],
FILTER(EPOS,EPOS[Brand Name]=“Bolt”),
SAMEPERIODLASTYEAR(‘Calendar’[Date]))
With the filter it didn’t work, why? , but when you use it without the Filter as in your formula it works…what am I missing here…

9

I just saw your video and it is a fantastic explanation of Expanded tables…Great!!

1 Like