cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Basti76
Frequent Visitor

Show certain value only belonging to the earliest date

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! 🙂

8 REPLIES 8
mahoneypat
Super User IV
Super User IV

You could try using FIRSTNONBLANKVALUE as follows

 

First Qty = FIRSTNONBLANKVALUE(POtable[Delivery Date], MIN(POtable[Qty]))

 

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI 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

 

mahoneypat_0-1612445194509.png

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI 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.

 

Basti76
Frequent Visitor

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". 
 

EarliestQty = RELATEDTABLE(OpenPO,MIN(OpenPO[Billing Date]))

(Too many arguments were passed to the RELATEDTABLE function. The maximum argument count for the function is 1.)

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@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. 

 

 

amitchandak
Super User IV
Super User IV

@Basti76 , Try a new column like

 

relatedtable(purchase, min(purchase[delivery_date])

or

minx(filter(purchase,purchase[sku] =item[sku]),purchase[delivery_date])



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors