Forum Discussion
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 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 |
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
6 Replies
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 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")
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 Dale,
Works like a charm, would you have time to let me know how this works?
Thanks
Z