Reply
Frequent Visitor
Posts: 2
Registered: ‎12-06-2018
Accepted Solution

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

 

Can someone please help me?


Accepted Solutions
Community Support Team
Posts: 1,116
Registered: ‎07-25-2018

Re: Finding the most recent value in another table

Hi @bfdijkstra

 

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

1.png

 

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.

View solution in original post


All Replies
Community Support Team
Posts: 1,116
Registered: ‎07-25-2018

Re: Finding the most recent value in another table

Hi @bfdijkstra

 

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

1.png

 

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
Posts: 2
Registered: ‎12-06-2018

Re: Finding the most recent value in another table

Hi @v-cherch-msft,

 

Thanks! That worked well for me!