cancel
Showing results for
Did you mean:
Frequent Visitor

## Finding the most recent value in another table

Hi everyone,

I'm trying to find a solution for the following issue.

I have two tables:

Salesinvoice (salesdate, articlenumber)

Purchaseinvoice (purchasedate, articlenumber, purchaseprice)

I'm trying to create a measure in Salesinvoice that, based on the salesdate, selects the latest purchaseprice.

So, the salesdate >= purchasedate && MAX(purchasedate).

1 ACCEPTED SOLUTION

Accepted Solutions
Super Contributor

## Re: Finding the most recent value in another table

You may use below measure. Show the simplified sample as below:

```LatestPrice =
CALCULATE (
MAX ( Purchaseinvoice[purchaseprice] ),
TOPN (
1,
FILTER (
Purchaseinvoice,
Purchaseinvoice[articlenumber] = MAX ( Salesinvoice[articlenumber] )
&& Purchaseinvoice[purchasedate] <= MAX ( Salesinvoice[salesdate] )
),
Purchaseinvoice[purchasedate], DESC
)
)
```

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Super Contributor

## Re: Finding the most recent value in another table

You may use below measure. Show the simplified sample as below:

```LatestPrice =
CALCULATE (
MAX ( Purchaseinvoice[purchaseprice] ),
TOPN (
1,
FILTER (
Purchaseinvoice,
Purchaseinvoice[articlenumber] = MAX ( Salesinvoice[articlenumber] )
&& Purchaseinvoice[purchasedate] <= MAX ( Salesinvoice[salesdate] )
),
Purchaseinvoice[purchasedate], DESC
)
)
```

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: Finding the most recent value in another table

Thanks! That worked well for me!

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 134 members 1,713 guests
Recent signins: