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.
I have a table loaded from SQL server using import into Power Bi. This table contains all of our historical and open PO information.
I've created measures to calculate what the next incoming PO date is for each item:
NEXT PO DATE = CALCULATE(
MIN(PURCHASING[PROMISE DATE]),
IF(
PURCHASING[PROMISE DATE] >= DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW())),
1,
BLANK()
)
)
I've also created a mesure to calculate how much is on the next incoming PO:
NEXT PO QTY = CALCULATE(
SUM('ON ORDER BY DATE'[QTY ON ORDER]),
FIRSTDATE('ON ORDER BY DATE'[PROMISE DATE])
)
The query loads in a column which shoes the shipping method for each PO. What I can't figure out how to do is how to show a, for each item, all 3 pieces: What the next PO date is, the quantity on that PO, and how it arrives. The first two are solved with the above measures, but I can't figure out how to show the shipping method for that PO. I can't simply insert the "shipping method" column from the query since there are many shipping methods depending on the PO. Doing so returns this:
As you can see from this picture, the correct shipping method for the next PO is "Routing Guide", but I end up seeing all of the potential options.
Any ideas?
Solved! Go to Solution.
I found a solution:
Because I had already created a calculated table, defined as follows:
ON ORDER BY DATE = CALCULATETABLE(
Purchasing,
SUMMARIZECOLUMNS(Purchasing[PROMISE DATE]),
Purchasing[PROMISE DATE] >= TODAY()
)
I was able to use this, and when I add the column "Shipping Method", use the Power Bi selection of "first" since this calcualated table only brings in PO's that are greater than the current date. So the "first" in the column would be for the most recent PO.
I found a solution:
Because I had already created a calculated table, defined as follows:
ON ORDER BY DATE = CALCULATETABLE(
Purchasing,
SUMMARIZECOLUMNS(Purchasing[PROMISE DATE]),
Purchasing[PROMISE DATE] >= TODAY()
)
I was able to use this, and when I add the column "Shipping Method", use the Power Bi selection of "first" since this calcualated table only brings in PO's that are greater than the current date. So the "first" in the column would be for the most recent PO.
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |