Forum Discussion
Solved
Finding the last offered price
As you can see in the attached file. We have the current price and various offers. I want to calculate the following: - The last price offered. - The final price. This is the last price offered or,...
Roli_Bird What is it that you expect to see in the yellow shaded totals? The following measure will sum the final Prices column, resulting in 291.
=CALCULATE(sumx(tblPrices,IF([Latest Offer]<>BLANK(),[Latest Offer],[Actual Price])))
If do not show Grand Total for Latest Price
it could be
Latest Offer A:=
VAR maxDate =
MAX ( tblOffers[OfferDate] )
RETURN
IF (
ISFILTERED ( tblItems[ItemCode] ),
CALCULATE (
VALUES ( tblOffers[OfferedPrice] ),
tblOffers[OfferDate] = maxDate
),
BLANK ()
)
Final Price A:=
VAR price =
IF (
[Latest Offer A] = 0,
[Actual Price],
[Latest Offer A]
)
RETURN
IF (
ISFILTERED ( tblItems[ItemCode] ),
price,
SUMX (
tblItems,
IF (
[Latest Offer A] = 0,
[Actual Price],
[Latest Offer A]
)
)
)
That's a very good solution. I like how you structure your code. But when we filter the data, the result is no longer correct.
I modified your code as follows.
Latest Offer B:=VAR maxDate = MAX ( tblOffers[OfferDate] ) RETURN IF ( HASONEVALUE( tblItems[ItemCode] ); CALCULATE ( VALUES ( tblOffers[OfferedPrice] ); tblOffers[OfferDate] = maxDate ); BLANK () ) Final Price B:=VAR price = IF ( [Latest Offer A] = 0; [Actual Price]; [Latest Offer A] ) RETURN IF ( HASONEVALUE( tblItems[ItemCode] ); price; SUMX ( tblItems; IF ( [Latest Offer A] = 0; [Actual Price]; [Latest Offer A] ) ) )Now the result looks better. See also the attached file.
Please do not get me wrong. Your solution is great. I would never have come up with your solution and only made a small change to YOUR measure.
Thank you for your support and solution.
Yes, you are right, HASONVALUE() gives more correct result.
