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.
Hello there,
I have two tables. One table amongst other data as a sku number (no double entries). In another table I have purchase orders. Usually there are severals purchase orders for the same sku.
Now I would like to see for each sku the earliest delivery date and the corresponding quantity. I have no problem with the date but of course for the quantity the entire quantity from all POs is shown. How could I filter in the report to only show the quantity corresponding to the shown earliest date?
Thanks for your help! 🙂
You could try using FIRSTNONBLANKVALUE as follows
First Qty = FIRSTNONBLANKVALUE(POtable[Delivery Date], MIN(POtable[Qty]))
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@mahoneypat Unfortunatly that did not work . It is a simple table like that.
SKU Delivery Date Quantity
A 02.03.2021 15
B 03.03.2021 25
C 26.02.2021 10
A 05.05.2021 30
B 06.07.2021 100
A 08.10.2021 45
C 23.07.2021 30
For a visual a pull for each Product the earliest delivery date but I want to add the quantity. If I add the Quantity I get the total of all POs not only for that earliest date. Desired outcome would be something like this:
A - 02.03.2021 - 15
B - 03.03.2021 - 25
C - 26.02.201 - 10
Here is what I get on my end with a measure like that. Seems to work. Please confirm.
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi Pat, thanks for getting back. Yes, it works in the one individual filter. Unfortunately if I put it in my visual where I combine it with other tables I get several lines per sku with the right quantity.
All tables are joined to one SKU-table (one to many). I am afraid I will have to investigate further.
Thank you @amitchandak. I am a new user and haven´t added any columns before.
1. relatedtable only lets me add the table but not the "min".
2. How can I add the item[sku] to the sendond part? The "auto suggest does not add me the sku from the item table there.
Thanks!
@Basti76 , I not sure about the exact table names, so I used a few names.
refer my video https://www.youtube.com/watch?v=czNHt7UXIe8
@amitchandak Of course you wouldn´t know my exact table names 😉
But wouldn´t there be a way in the PO table to add a column that looks for the earliest date for the the sku in that row and only puts the quantity corresponding to that earliest date? In a visual I could then use the earliest delivery date and for the quantity the average.
@Basti76 , Try a new column like
relatedtable(purchase, min(purchase[delivery_date])
or
minx(filter(purchase,purchase[sku] =item[sku]),purchase[delivery_date])
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 |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |