Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Basti76
Helper I
Helper I

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
Employee
Employee

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
Helper I
Helper I

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

@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
Super User

@Basti76 , Try a new column like

 

relatedtable(purchase, min(purchase[delivery_date])

or

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.