Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to Solution.
Hi @Anonymous
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
Hi @Anonymous
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
User | Count |
---|---|
98 | |
89 | |
82 | |
71 | |
67 |
User | Count |
---|---|
114 | |
104 | |
101 | |
72 | |
65 |