Filter context not working on calculated variables

[Anonymous]

Hi

I have code as such - this is suppose to break down the expenses for north region:

    TotalExpense = 
    VAR _percentage = SUM(north%)
    VAR _goodexpense = SUM(expense[allgoodexpense])* _percentage
    VAR _fuelexpense = SUM(expense[allfuel])* _percentage
    VAR _total = _goodexpense + _fuelexpense
    RETURN CALCULATE(_total, 
    FILTER(ALL(FYDate), FYDate[RankFinYYWW] =  MAX(FYDate[RankFinYYWW])-6
                        && FYDate[RankFinYYWW] =  MAX(FYDate[RankFinYYWW]) -1))

Which should filter my visual between last week and 6 weeks previous.
But at the moment its returning all weeks I have in my Calender, I only want the hightlighted yellow

Not sure where I went wrong here as the filter works fine for other measures example:

AllTotalExpense =
CALCULATE(SUM(expense[allgoodexpense]), 
FILTER(ALL(FYDate),
    FYDate[RankFinYYWW] >= MAX(FYDate[RankFinYYWW])-6 &&
     FYDate[RankFinYYWW] <= MAX(FYDate[RankFinYYWW])-1)))

Please help - let me know if you need sample data as I can create some mockup data as well. 

[Anonymous · Accepted solution]

Thanks, 

I was able to achieve the filter by filtering each VAR individually like so 

totalexpensenorthVAR = 
VAR _percentagenorth = 
CALCULATE(SUM(expense[north]),
FILTER(ALL(FYDate),
    FYDate[RankFinYYWW] >= MAX(FYDate[RankFinYYWW])-2 &&
    FYDate[RankFinYYWW] <= MAX(FYDate[RankFinYYWW])-1))

VAR _goodexpense =
CALCULATE(SUM(expense[allexpense]),
FILTER(ALL(FYDate),
    FYDate[RankFinYYWW] >= MAX(FYDate[RankFinYYWW])-2 &&
    FYDate[RankFinYYWW] <= MAX(FYDate[RankFinYYWW])-1))

VAR _fuelexpense =CALCULATE(SUM(expense[fuelexpense]),
FILTER(ALL(FYDate),
    FYDate[RankFinYYWW] >= MAX(FYDate[RankFinYYWW])-2 &&
    FYDate[RankFinYYWW] <= MAX(FYDate[RankFinYYWW])-1))

VAR _total = (_goodexpense+_fuelexpense)*_percentagenorth

RETURN _total

[HashamNiaz · Accepted solution]

Hi @Anonymous !

There are 2 problems with the DAX code you have written; Let's replace all VAR with explicit measures [_total];

_total = 
    VAR _percentagenorth = SUM(expense[north])
    VAR _goodexpense = SUM(expense[allexpense]) * _percentagenorth
    VAR _fuelexpense = SUM(expense[fuelexpense]) * _percentagenorth

RETURN  _goodexpense +_fuelexpense

totalexpensenorth = 
CALCULATE([_total], 
    FILTER(ALL(FYDate), FYDate[RankFinYYWW] >=  MAX(FYDate[RankFinYYWW])-2
                        && FYDate[RankFinYYWW] <=  MAX(FYDate[RankFinYYWW]) -1))

The other problem in your DAX was with >= & <= sign, you were using = sign for both conditions earlier.

Hope this will resolve.

Regards,

Hasham

[Anonymous]

Thanks for the detail explaination i assume i would have to write this as two measures?

[HashamNiaz]

Yes @Anonymous  

These are 2 measures.

[Kucrapok]

@Anonymous When you create a variable like this :
VAR _Val = Calculate( ... )

It will be considered as a constant so you cannot use this variable in a future Calculate expression like this :
Calculate( _Val, Filter(...)  )

You need to remove these variable and keep the original SUM expression in the calculate function

[Anonymous · Accepted solution]

Thanks, 

I was able to achieve the filter by filtering each VAR individually like so 

totalexpensenorthVAR = 
VAR _percentagenorth = 
CALCULATE(SUM(expense[north]),
FILTER(ALL(FYDate),
    FYDate[RankFinYYWW] >= MAX(FYDate[RankFinYYWW])-2 &&
    FYDate[RankFinYYWW] <= MAX(FYDate[RankFinYYWW])-1))

VAR _goodexpense =
CALCULATE(SUM(expense[allexpense]),
FILTER(ALL(FYDate),
    FYDate[RankFinYYWW] >= MAX(FYDate[RankFinYYWW])-2 &&
    FYDate[RankFinYYWW] <= MAX(FYDate[RankFinYYWW])-1))

VAR _fuelexpense =CALCULATE(SUM(expense[fuelexpense]),
FILTER(ALL(FYDate),
    FYDate[RankFinYYWW] >= MAX(FYDate[RankFinYYWW])-2 &&
    FYDate[RankFinYYWW] <= MAX(FYDate[RankFinYYWW])-1))

VAR _total = (_goodexpense+_fuelexpense)*_percentagenorth

RETURN _total

[Ashish_Mathur]

Hi,

Unable to understand your question.  Share the link from where i can download your PBI file and clearly show the expected resut there.

[Anonymous]

Hi I have made a sample PBIX file.

First time trying to share it so let me know if it doesnt work. 

sample file.pbx