How do I Calculate reduced qty

[rohannimje]

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.

[v-zhangti · Accepted solution]

Hi, @rohannimje 

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
PreTotal

PendingQty 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.

[Greg_Deckler]

@rohannimje 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 - __BilledQty