SOLVED

Finding the last offered price

Occasional Contributor

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, if this is missing, the current price.

With my measures 'Latest Offer' and 'Final Price' I can calculate the desired values.

The problem is the Grand Total, which shows an incorrect value for the 'Final Price'.

5 Replies
best response confirmed by Roli_Bird (Occasional Contributor)
Solution

Re: Finding the last offered price

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

Re: Finding the last offered price

Thank you very much. 291 is what I expect to see - and that's what your measure calculates.

My next problem is just understanding your measure. You use CALCULATE, without filter and inside CALCULATE you use SUMX in table tblPrices with my original formula as expression.

Could you please explain in two or three sentences how you came up with this measure or how the measure works.

Re: Finding the last offered 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]
)
)
)

``````

Re: Finding the last offered price

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