Filter in specific order

[zibster]

Hi, I need to filter out sub_account just to 100 and than find MAX in YEAR_PD and return value from Custom column, I know how to find MAX value but dont know how to apply firs filter to the YEAR_PD the calculation should return last row as valid row.

YEAR_PD	SUB_ACCOUNT	DESCRIPTION	FAC	DEPT	SECT	AMT	YearPerStoreSect	Spread2018T4.%	Custom
2018 04	312166	INVENTORY	8	301	301		2018 040008301		($160,886.00)
2016 07	100	INVENTORY COUNT	8	301	301	139441	2016 070008301	65.29%	$139,441.00
2016 13	100	INVENTORY COUNT	8	301	301	149026	2016 130008301	64.39%	$149,026.00
2017 07	100	INVENTORY COUNT	8	301	301	146470	2017 070008301	65.12%	$146,470.00
2017 13	100	INVENTORY COUNT	8	301	301	139369	2017 130008301	65.11%	$139,369.00

[zibster]

Hi,

I am trying to filter first all accounts down to only 100 and then return only the value with the max year_pd? the below returns correct Year_PD  but i don't know how to filter the account please help.

Thanks

Z

Calculations:=CALCULATE(SUM(Inv2018T[Custom]),
                FILTER(ALL(Inv2018T[YEAR_PD]),Inv2018T[YEAR_PD]=MAX(Inv2018T[YEAR_PD]))
                )

YEAR_PD	SUB_ACCOUNT	DESCRIPTION	FAC	DEPT	SECT	AMT	YearPerStoreSect	Spread2018T4.%	Custom
2018 04	312166	INVENTORY-MERCHANDISE-CLOSING	8	301	301		2018 040008301		($160,886.00)
2016 07	100	INV OVER/(SHORT) - TOTAL INVENTORY COUNT BY DEPT	8	301	301	139441	2016 070008301	65.29%	$139,441.00
2016 13	100	INV OVER/(SHORT) - TOTAL INVENTORY COUNT BY DEPT	8	301	301	149026	2016 130008301	64.39%	$149,026.00
2017 07	100	INV OVER/(SHORT) - TOTAL INVENTORY COUNT BY DEPT	8	301	301	146470	2017 070008301	65.12%	$146,470.00
2017 13	100	INV OVER/(SHORT) - TOTAL INVENTORY COUNT BY DEPT	8	301	301	139369	2017 130008301	65.11%	$139,369.00

[zibster]

Here is my formula but returns (blank)

Calculations:=CALCULATE(SUM(Inv2018T[Custom]),FILTER(ALL(Inv2018T[YEAR_PD]),Inv2018T[YEAR_PD]=MAX(Inv2018T[YEAR_PD])),Inv2018T[SUB_ACCOUNT]="100")

[v-jiascu-msft · Accepted solution]

Hi @zibster,

Try this formula please.

Calculations =
VAR maxYearPD =
    CALCULATE ( MAX ( Inv2018T[YEAR_PD] ), Inv2018T[SUB_ACCOUNT] = 100 )
RETURN
    CALCULATE (
        SUM ( Inv2018T[Custom] ),
        FILTER ( ALL ( Inv2018T[YEAR_PD] ), Inv2018T[YEAR_PD] = maxYearPD ),
        Inv2018T[SUB_ACCOUNT] = 100
    )

Best Regards,

Dale

[zibster]

Hi Dale,

Works like a charm, would you have time to let me know how this works?

Thanks

Z

[v-jiascu-msft]

Hi @zibster,

So glad it helps. 

Please refer to the comments in the formula.

Calculations =
// We need the max [YEAR_PD] of [SUB_ACCOUNT] 100; CALCULATE changes the contexts of MAX, which
// means we changes the calculation scope of MAX.
VAR maxYearPD =
    CALCULATE ( MAX ( Inv2018T[YEAR_PD] ), Inv2018T[SUB_ACCOUNT] = 100 )
RETURN
// We still use CALCULATE to change the context of SUM. We can set Inv2018T[SUB_ACCOUNT] = 100 in 
// case that other SUB_ACCOUNT has the maxYearPD. 
    CALCULATE (
        SUM ( Inv2018T[Custom] ),
        FILTER ( ALL ( Inv2018T[YEAR_PD] ), Inv2018T[YEAR_PD] = maxYearPD ),
        Inv2018T[SUB_ACCOUNT] = 100
    )
// In short, we reset the calculation scope.

Best Regards,

Dale

[zibster]

Thank you very much.