Forum Discussion
How do I Calculate reduced qty
How to calculate the pending qty.
My measure to calculate pending qty = [TotalOrderedQty] - [TotalBilledQty]
But the results are not satisfactory.
| ORDER NO | MATERIAL CODE | TotalOrderdQty | BILLED QUANTITY | MonthInCalendar | PendingQty A | PendingQty B | Date |
| 4.51E+09 | 90003847 | 3750 | Jun-22 | -3750 | 0 | 10-06-2022 00:00 | |
| 4.51E+09 | 90003847 | 4350 | May-22 | -4350 | 3750 | 20-05-2022 00:00 | |
| 4.51E+09 | 90003847 | 8100 | Nov-21 | 8100 | 03-11-2021 00:00 |
Above table shows the 2 pendingQty column
PendingQty A - is the results I get
PendingQty B - is the results which I expected.
So anyone please help me that how should I get the result as shown in "pendnigQty B" column.
Hi, Anonymous
You can try the following methods.
PreTotal = Var PrevDate=MAXX(FILTER(ALL('Table'[Date]),'Table'[Date]<SELECTEDVALUE('Table'[Date])),[Date]) Var PreTotal=CALCULATE([TotalOrderdQty],FILTER(ALL('Table'),[Date]=PrevDate)) Return PreTotalPendingQty B = IF([PreTotal]=BLANK(),BLANK(),[PreTotal]-[TotalBilledQty])Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Anonymous You will need something like:
Pending B Column = VAR __OrderNo = [ORDER NO] VAR __Material = [MATERIAL CODE] VAR __BilledQty = [BILLED QUANTITY] VAR __Date = [Date] VAR __Table = FILTER(ALL('Table'),[ORDER NO] = __OrderNo && [MATERIAL CODE] = __Material) VAR __TotalOrderedQty = SUMX(__Table, [TotalOrderedQty] VAR __Table2 = FILTER(__Table,[Date] <= __Date) VAR __BilledQty = SUMX(__Table2, [BILLED QUANTITY]) RETURN __TotalOrderedQty - __BilledQtyHi, Anonymous
You can try the following methods.
PreTotal = Var PrevDate=MAXX(FILTER(ALL('Table'[Date]),'Table'[Date]<SELECTEDVALUE('Table'[Date])),[Date]) Var PreTotal=CALCULATE([TotalOrderdQty],FILTER(ALL('Table'),[Date]=PrevDate)) Return PreTotalPendingQty B = IF([PreTotal]=BLANK(),BLANK(),[PreTotal]-[TotalBilledQty])Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
