Forum Discussion
Solved
Filter in specific order
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 t...
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
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 |