Forum Discussion
pankajj
6 years agoHelper III
Calculate Reduced balance quantity
Hi Community! Is there a quick way in Power BI / DAX to calculate remaining quantity? I have following sample table where i need to calculate balance quantity (REMAIN_QTY). In my table i have...
- 6 years ago
So, perhaps you want something like the following:
Column = VAR __PromiseDate = 'Table'[PROMISE DATE] VAR __SupplyDate = 'Table'[Supply_Date] VAR __TotalSoFar = SUMX( FILTER( 'Table', 'Table'[Supply_Date] < EARLIER('Table'[Supply_Date]) ), 'Table'[ALLOTTED_QTY] ) VAR __OrderQty = SUMX( FILTER( 'Table', 'Table'[Demand_Order] = EARLIER('Table'[Demand_Order]) ), 'Table'[ORDER QTY] ) VAR __PreText = SWITCH( TRUE(), __TotalSoFar < __OrderQty,"Partial Quantity (" & 'Table'[ALLOTTED_QTY] & ") ", __TotalSoFar = __OrderQty,"Final Quantity (" & 'Table'[ALLOTTED_QTY] & ") ", BLANK() ) VAR __PostText = SWITCH( TRUE(), __SupplyDate < __PromiseDate,"Arriving Early on " & 'Table'[Supply_Date] & " for " & 'Table'[Demand_Order], __SupplyDate > __PromiseDate,"Arriving Late on " & 'Table'[Supply_Date] & " for " & 'Table'[Demand_Order], BLANK() ) RETURN __PreText & __PostTextPBIX is attached.
- 6 years ago
Try like
Cal remaining = sumx('Table','Table'[ORDER QTY])-sumx(filter('Table','Table'[ORDER-LINE]=EARLIER('Table'[ORDER-LINE]) && 'Table'[Supply_Date]<=EARLIER('Table'[Supply_Date])),'Table'[ALLOTTED_QTY])Add additional filter like demand order if needed
- 6 years ago
Couple necessary fixes to the right supply date field and an equals sign. I think I got your change right as well, PBIX attached.
Column = VAR __PromiseDate = 'Table'[PROMISE DATE] VAR __SupplyDate = 'Table'[SupplyDate] VAR __TotalSoFar = SUMX( FILTER( 'Table', 'Table'[SupplyDate] <= EARLIER('Table'[SupplyDate]) && 'Table'[ORDER-LINE] = EARLIER('Table'[ORDER-LINE]) ), 'Table'[ALLOTTED_QTY] ) VAR __OrderQty = SUMX( FILTER( 'Table', 'Table'[Demand_Order] = EARLIER('Table'[Demand_Order]) && 'Table'[ORDER-LINE] = EARLIER('Table'[ORDER-LINE]) ), 'Table'[ORDER QTY] ) VAR __PreText = SWITCH( TRUE(), __TotalSoFar < __OrderQty,"Partial Quantity (" & 'Table'[ALLOTTED_QTY] & ") ", __TotalSoFar = __OrderQty,"Final Quantity (" & 'Table'[ALLOTTED_QTY] & ") ", BLANK() ) VAR __PostText = SWITCH( TRUE(), __SupplyDate < __PromiseDate,"Arriving Early on " & 'Table'[Supply_Date] & " for " & 'Table'[Demand_Order], __SupplyDate > __PromiseDate,"Arriving Late on " & 'Table'[Supply_Date] & " for " & 'Table'[Demand_Order], BLANK() ) RETURN IF(ISBLANK(__SupplyDate),[Supply_Date],__PreText & __PostText)
Greg_Deckler
6 years agoCommunity Champion
Couple necessary fixes to the right supply date field and an equals sign. I think I got your change right as well, PBIX attached.
Column =
VAR __PromiseDate = 'Table'[PROMISE DATE]
VAR __SupplyDate = 'Table'[SupplyDate]
VAR __TotalSoFar =
SUMX(
FILTER(
'Table',
'Table'[SupplyDate] <= EARLIER('Table'[SupplyDate]) &&
'Table'[ORDER-LINE] = EARLIER('Table'[ORDER-LINE])
),
'Table'[ALLOTTED_QTY]
)
VAR __OrderQty =
SUMX(
FILTER(
'Table',
'Table'[Demand_Order] = EARLIER('Table'[Demand_Order]) &&
'Table'[ORDER-LINE] = EARLIER('Table'[ORDER-LINE])
),
'Table'[ORDER QTY]
)
VAR __PreText =
SWITCH(
TRUE(),
__TotalSoFar < __OrderQty,"Partial Quantity (" & 'Table'[ALLOTTED_QTY] & ") ",
__TotalSoFar = __OrderQty,"Final Quantity (" & 'Table'[ALLOTTED_QTY] & ") ",
BLANK()
)
VAR __PostText =
SWITCH(
TRUE(),
__SupplyDate < __PromiseDate,"Arriving Early on " & 'Table'[Supply_Date] & " for " & 'Table'[Demand_Order],
__SupplyDate > __PromiseDate,"Arriving Late on " & 'Table'[Supply_Date] & " for " & 'Table'[Demand_Order],
BLANK()
)
RETURN
IF(ISBLANK(__SupplyDate),[Supply_Date],__PreText & __PostText)
pankajj
6 years agoHelper III
Hi Greg,
This works perfect now.
Have tried it with my big data set and runs smoothly.
Thanks a ton for your support. 🙏
You are a genius!