Forum Discussion
Calculate Reduced balance quantity
- 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)
Hi Greg!
This is perfect, I have replaced Blank() with [Supply_Date].
One more thing, the column is still showing Partial Quantity (10) Arriving Late on May15 2020 for SO-AB1234/1 instead of Final Quantity(10) Arriving on May15 2020 forSO-AB1234/1.
Kindly check.
Sorry Im asking too much.
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)
- pankajj6 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!