Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello Community,
First time poster but been lurking in the forum for a couple of years, I'm writing this post because I don't know how to frame this on a google search and also don't know where to start on this formula that iI have been tasked to create.
In the first table we have a table with two "orders" with quantity and an ETA on when they would be received.
On August 7th we will receive 2 pieces of SKU ABC and then 2 more pieces on August 10th
SKU | QTY | ETA |
ABC | 2 | 8/7/2021 |
ABC | 2 | 8/10/2021 |
I need a formula that could compute the ETA according to a set of rules:
1. Priority - Lower Number has higher priority
2. Order Date - Oldest Date has higher priority
In this we have two orders with priority 1, so they should get the first ETA of 08/07/2021, even though one of the orders has a later date than some of the orders, the priority takes presedence over the order date.
The second order of 2 pieces on 08/10/2021 should be considered and assign that date to the ETA column.
There are 3 orders pending with the same priority but only 2 pieces arriving, the next rule is based on order date so, the orders with an order date of 07/04/2021 should receive the following 2 pieces incoming and the order from 07/08/2021 will need to wait until the next shipment but since there's no more shipments in the data it should have a No ETA or blank comment.
SKU | Priority | Order Date | QTY | ETA |
ABC | 1 | 7/4/2021 | 1 | 8/7/2021 |
ABC | 2 | 7/4/2021 | 1 | 8/10/2021 |
ABC | 2 | 7/4/2021 | 1 | 8/10/2021 |
ABC | 1 | 7/7/2021 | 1 | 8/7/2021 |
ABC | 2 | 7/8/2021 | 1 | No ETA |
Hopefully the ask is clear, thank you very much for your assistance!
sitenfel
@sitenfel Power BI is a reporting tool, not a process management / material allocation tool. You would probably also want an audit trail of your allocation decisions. Power BI has no memory and is thus not a suitable tool for this (beyond some rudimentary forecasting).
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
25 | |
10 | |
8 | |
6 | |
6 |
User | Count |
---|---|
30 | |
11 | |
11 | |
10 | |
6 |