ignore filters when sum amount based on Max Year

[adityo]

Hi All,

I want to make calculations Current year*,that ignore my filter selections

*Current Year calculation is sum of amount of Max of Current year in AP_Database table 

here are my scheme :

Date.Clearing one to many AP_Database.Clearing

here are my measures
Current Year New =

var MaxYear = CALCULATE(MAX('Date'[Year Outgoing]), ALL('Date'[Year Outgoing]))

RETURN

CALCULATE(SUM(AP_Database[Amount in local cur.]),FILTER(ALL('Date'[Year Outgoing]), 'Date'[Year Outgoing]=MaxYear))

but it seems doesn't work.

When i filtered table Date, it also filtered the Current Year New

[v-jayw-msft]

Hi @adityo 

Current Year New =

var MaxYear = CALCULATE(MAX('Date'[Year Outgoing]), ALL('Date'[Year Outgoing]))

RETURN

CALCULATE(SUM(AP_Database[Amount in local cur.]),FILTER(ALL(AP_Database), YEAR(AP_Database[Clearing])=MaxYear))

You need to use the date in AP_Database to match the date in Date table.

Best Regards,

Jay

[Kucrapok]

Im aorey but this is one of the worst advice I saw on this forum.

For god sake, dont use filter( All(Ap_database)). What if this table has million rows, you will end up iterating on all the rows.

• Always filter colums in the dimensions and use the relationship.

The approach from @amitchandak is really better in this case.

[adityo]

@v-jayw-msft it seems work.... it calculates the current year. but i think it will mislead the context. while im using matrix for the visualization, all the number are same for all category....

[v-jayw-msft · Accepted solution]

Hi @adityo ,

It is because you are using ALL() function in the formula. Try using ALLEXCEPT() function instead.

measure 1 = CALCULATE(SUM(AP_Database[Amount in local cur.]),FILTER(ALLEXCEPT(AP_Database,AP_Database[category]), YEAR(AP_Database[Clearing])=MaxYear))

measure 2 = CALCULATE(SUM(AP_Database[Amount in local cur.]),FILTER(ALL(AP_Database), YEAR(AP_Database[Clearing])=MaxYear&&AP_Database[category]=SELECTEDVALUE(AP_Database[category])))

 If both the above measures don't work, please show some sample data and expected result to us.

Best Regards,

Jay

[adityo]

Thx @v-jayw-msft it works perfect!

[amitchandak]

@adityo , Try one of the two

Current Year New =
var MaxYear = year(today())
RETURN
CALCULATE(SUM(AP_Database[Amount in local cur.]),FILTER(ALL('Date'[Year Outgoing]), 'Date'[Year Outgoing]=MaxYear))

Current Year New =
var MaxYear = maxx(all('Date'), 'Date'[Year Outgoing])
RETURN
CALCULATE(SUM(AP_Database[Amount in local cur.]),FILTER(ALL('Date'[Year Outgoing]), 'Date'[Year Outgoing]=MaxYear))

[adityo]

@amitchandak thank you for your response, unfortunately both of them doesn't works. the DAX script doesn't return error. but it keep filtered.