Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have 2 tables on orders with the order validity date and in transit stock (stock reaching where the order will be serviced).
(using sample data to simplify for understanding)
I am looking for a final calculation like this in my final table -
KEY | Order valid till Date | order quantity | In transit stock reaching before this date(total_qty) | cumulative orders covered before this date(used_stock) | Stock reached which has not been used up(avail_stock) | order quantity of this date serviced (serviced) |
1 | 22-09-2020 | 10 | 30 | 0 | 30 | 10 |
1 | 23-09-2020 | 15 | 30 | 10 | 20 | 15 |
1 | 24-09-2020 | 20 | 30 | 25 | 5 | 5 |
I have done the calculation till column 4 in power BI
if this was in excel i could have simply done
used_stock(2) = serviced(1) + used_stock(1)
avail_stock(2) = total_qty(2) - used_stock(2)
serviced(2) = min(order(2),avail_stock(2))
My base tables look like this -
order table -
KEY | Order valid till Date | order quantity |
1 | 22-09-2020 | 10 |
1 | 23-09-2020 | 15 |
1 | 24-09-2020 | 20 |
in transit table -
Key | expected date of reaching | Quanity in shipment |
1 | 21-09-2020 | 30 |
1 | 25-09-2020 | 40 |
I have done the total_qty measure calculation by finding the cumulative sum of shipment quantity for the dates before selected value of order validity date.
I am trying to do the rest of the measures but ending up in circular references. Is there a way I can do it?
Hi @Anonymous ,
I used the following Sample data.
order table:
KEYOrder valid till Dateorder quantity
1 | 9/22/2020 | 10 |
1 | 9/23/2020 | 25 |
1 | 9/24/2020 | 20 |
in transit table:
KeyQuanity in shipmentexpected date of reaching
1 | 30 | 9/21/2020 |
1 | 20 | 9/24/2020 |
Create these measures.
total_qty =
CALCULATE (
SUM ( 'in transit table'[Quanity in shipment] ),
FILTER (
'in transit table' ,
[expected date of reaching] <= MAX ( 'order table'[Order valid till Date] )
)
)
used_stock =
VAR _Calc =
CALCULATE (
SUM ( 'order table'[order quantity] ),
FILTER (
ALL ( 'order table' ),
[Order valid till Date] < MAX ( 'order table'[Order valid till Date] )
)
)
VAR _Quanity =
CALCULATE (
SUM ( 'in transit table'[Quanity in shipment] ),
FILTER (
'in transit table' ,
[expected date of reaching] < MAX ( 'order table'[Order valid till Date] )
)
)
RETURN
IF ( _Calc + 0 > _Quanity, _Quanity, _Calc + 0 )
avail_stock =
[total_qty] - [used_stock]
serviced =
MIN ( MAX ( 'order table'[order quantity] ), [avail_stock] )
The result is this.
You can check more details from here.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
let me test it. I will get back to you
@Anonymous - See attached PBIX below sig, Table 2, 2a and 2b. On Page 2. I created the following measures:
total_qty = SUMX(FILTER(RELATEDTABLE('Table (2a)'),[expected date of reaching]<MAX([Order valid till Date])),[Quanity in shipment])
used_stock =
VAR __Date = MAX([Order valid till Date])
VAR __Calc = SUMX(FILTER(ALL('Table (2)'),[Order valid till Date]<__Date),[order quantity])
RETURN
__Calc+0
avail_stock = [total_qty] - [used_stock]
serviced = MIN(MAX([order quantity]),[avail_stock])
@Greg_Deckler thanks a lot for your reply.
(edit 2 - in short, used stock should be sum of serviced rather than sum of orders which will result in circular dependency)
But, these measures will only give me the correct result for this data in which orders were less than the available stock. If the order becomes greater than available stock, your sumx for calculating used stock will be wrong as the used stock should only be sum of min(order,availble stock) and not just sum of order quanitity as used_stock can't be greater than available stock.
For example if the data had 2nd order as 25 instead of 15, your mesures will show -
KEY | Order valid till Date | order quantity | In transit stock reaching before this date(total_qty) | cumulative orders covered before this date(used_stock) | Stock reached which has not been used up(avail_stock) | order quantity of this date serviced (serviced) |
1 | 22-09-2020 | 10 | 30 | 0 | 30 | 10 |
1 | 23-09-2020 | 25 | 30 | 10 | 20 | 20 |
1 | 24-09-2020 | 20 | 30 | 35 | -5 (?) | -5 |
whereas it actually should show up like this -
KEY | Order valid till Date | order quantity | In transit stock reaching before this date(total_qty) | cumulative orders covered before this date(used_stock) | Stock reached which has not been used up(avail_stock) | order quantity of this date serviced (serviced) |
1 | 22-09-2020 | 10 | 30 | 0 | 30 | 10 |
1 | 23-09-2020 | 25 | 30 | 10 | 20 | 20 |
1 | 24-09-2020 | 20 | 30 | 30 | 0 | 0 |
Basically, the sumx on used stock should be on min(order quantity, avail_stock) but this results in circular dependency error.
We might still handle these -5 by making it zero if the result is negative, but if a new shipment arrives on 24-09 it will mess up the avail_stock as then it will add a erronous -5.
(edit - Just fyi, orders don't carry forward, they are only valid till the validity date mentioned)
@Anonymous Not clear on this. Seemed like I was getting all the right answers. Perhaps you could provide an expanded set of sample data and expected output?
See the following data and the expected output and the output your measures will give -
KEY | Order valid till Date | order quantity |
1 | 22-09-2020 | 10 |
1 | 23-09-2020 | 25 |
1 | 24-09-2020 | 20 |
Key | expected date of reaching | Quanity in shipment |
1 | 21-09-2020 | 30 |
1 | 24-09-2020 | 20 |
expected output -
KEY | Order valid till Date | order quantity | total_qty | used_stock | avail_stock | serviced |
1 | 22-09-2020 | 10 | 30 | 0 | 30 | 10 |
1 | 23-09-2020 | 25 | 30 | 10 | 20 | 20 |
1 | 24-09-2020 | 20 | 50 | 30 | 20 | 20 |
your measures output -
KEY | Order valid till Date | order quantity | total_qty | used_stock | avail_stock | serviced |
1 | 22-09-2020 | 10 | 30 | 0 | 30 | 10 |
1 | 23-09-2020 | 25 | 30 | 10 | 20 | 20 |
1 | 24-09-2020 | 20 | 50 | 35 | 15 | 15 |
Again please note I only want correct calculation of the serviced measure, rest is just the intermediate logic built by me. So, I'm open to a different logic to get to the serviced column.
@Greg_Deckler
for anyone trying to solve this, I just want the correct calculation of serviced column, the rest columns are intermediate created by me as logic. If there is other elegant logic to reach the serviced column, that would be brilliant as it looks like my logic will inevitably result in a circular dependency
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
69 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |