Apply a couple of filters on calculation

[Anonymous]

I want to make stock balance by LLITEM & Facility applying a couple of filters but for some reason "Balance" was calculated for all showing LLITEM & Facility.

How can i fix it to calculate stock balance by LLITEM & Facility?

Is it for "ALLSELECTED" ?

[Anonymous · Accepted solution]

Thanks for your kind advice.

 But if I removed "Allselected", cumulative balance became not applyed even balance show by item & facility.

 What I want to get is cumulative balance by week.

For example, Item.ABC has balance of WK20 to "1" for Inv_Qty "1" and then next week 2 of Demand Qty followed up without Inv_Qty & Transit Qty. If that, ideally "-1" show up. But if I removed "Allselected", just "-2" show up as not applied previous weeks' value.

  What I used : Balance = CALCULATE(SUM(Append1[Inv_Qty])-SUM(Append1[Demand Qty])+SUM(Append1[Transit_Qty]),FILTER(Append1,Append1[Week ending] <= MAX(Append1[Week ending])))

 What should I do to get cumulative balance by week of each item and facility?

[v-diye-msft · Accepted solution]

Hi @Anonymous ,

Try this one:

 Balance = CALCULATE(SUM(Append1[Inv_Qty])-SUM(Append1[Demand Qty])+SUM(Append1[Transit_Qty]),FILTER(ALLSELECTED(Append1),Append1[Week ending] <= MAX(Append1[Week ending])), VALUES(Append[LLITFM]),VALUES(Append[Facility]))

[v-diye-msft · Accepted solution]

Hi @Anonymous ,

Try this one:

 Balance = CALCULATE(SUM(Append1[Inv_Qty])-SUM(Append1[Demand Qty])+SUM(Append1[Transit_Qty]),FILTER(ALLSELECTED(Append1),Append1[Week ending] <= MAX(Append1[Week ending])), VALUES(Append[LLITFM]),VALUES(Append[Facility]))

[Anonymous · Accepted solution]

Highly thanks, Dina for your big help.

 Now it resolved as I revised it as you recommend 🙂 Fantastic. !!

[parry2k]

@Anonymous i don't think you need allselected in your dax expression, remove that and check.

[Anonymous · Accepted solution]

Thanks for your kind advice.

 But if I removed "Allselected", cumulative balance became not applyed even balance show by item & facility.

 What I want to get is cumulative balance by week.

For example, Item.ABC has balance of WK20 to "1" for Inv_Qty "1" and then next week 2 of Demand Qty followed up without Inv_Qty & Transit Qty. If that, ideally "-1" show up. But if I removed "Allselected", just "-2" show up as not applied previous weeks' value.

  What I used : Balance = CALCULATE(SUM(Append1[Inv_Qty])-SUM(Append1[Demand Qty])+SUM(Append1[Transit_Qty]),FILTER(Append1,Append1[Week ending] <= MAX(Append1[Week ending])))

 What should I do to get cumulative balance by week of each item and facility?