Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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 |
Solved! Go to 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
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
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
Thank you very much.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
107 | |
99 | |
91 | |
69 |
User | Count |
---|---|
173 | |
135 | |
131 | |
96 | |
94 |