Finding the last offered price

[Roli_Bird]

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

[#2 · Accepted solution]

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

[#3]

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.

[#4]

@Roli_Bird 

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

[#5]

@Sergei Baklan 

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.

[#6]

@Roli_Bird 

Yes, you are right, HASONVALUE() gives more correct result.