Date of Last Sale with Calculated Sale Price

Hi,
Pulling my hair out trying to work out how to make the evaluation context work properly.

I’m trying to work out and display

  1. Date of the most recent sale; and
  2. Calculated price per unit of the item(s) bought in that sale.

I’ve got a fairly standard sales table, a simplified redacted version is below:

it works but if i were to take the detail level away, eg Invoice_NUMBER I’d get the wrong result.

The max value for the price should be date 7/5/2019, price 94.35 but i get the numbers as displayed on the total line, eg price of 109.98

The ASP SELL CURRENCY formulais below:

ASP SELL CURRENCY =
CALCULATE (
    DIVIDE (
        SUM ( COMBINED_SALES_PLAN[CURR_REV] ),
        SUM ( COMBINED_SALES_PLAN[QUANTITY] ),
        BLANK ()
    ),
    FILTER ( COMBINED_SALES_PLAN, COMBINED_SALES_PLAN[TYPE] = “ACTUAL” ),
    FILTER ( COMBINED_SALES_PLAN, COMBINED_SALES_PLAN[DATE] = [Date of Last Sale] )
)

Going a little crazy here; would greatly appreciate any help!!!

Scheme and data below:
Data

Schema

@Nathan_Booth
Can you upload some sample data? Have a few ideas, but I would like to look at the data if possible

-Nick

Enterprise%20DNA%20Expert%20-%20Small

Agree some sample data would be helpful here.

The date of first sale should be very easy if you model is setup correctly.

All it should be is…

MIN( SalesDateColumn )

That’s it.

But obviously depends on the data setup, so if you could upload a demo that would be great.

Thanks

Scheme and data set below:

The date of last sale itself works but the ability to show the data of last sale AND the price charged on that sale is what’s tripping me up.

Date of Last Sale 2 = calculate (LASTDATE(COMBINED_SALES_PLAN[DATE]), filter (COMBINED_SALES_PLAN, COMBINED_SALES_PLAN[TYPE] = "actual"), filter (COMBINED_SALES_PLAN, COMBINED_SALES_PLAN[REV_RECOG_ADJ] <> "y"),keepfilters(ITEM_LIST))

Note that there’s some additional evaluation context, ie the type needs to be Actual as that removes any forecast, budget or unfilled orders.

i really want to be able to show a report like

RESULT

Your help would be appreciated.

I’ve added a PBI file.

I really want to be able to show the Calculated Sell Price for a particular item/customer where the Date the item was Sold is equal to the maximum value on the table.
In my example i’ve taken customer 21000004 with item 0006 where the item sold for $500 on 01 May, but sold for 720 on 19 March.
The Correct value would be
Last Sale Date = 01 May 2019
Sale Price = 500

I can then start using this to show measures like Average Sell Price for [Current Year] and the variance for each occurrence.

The Date of Last Sale calculation is each, being

Date of Last Sale = calculate (max(sales[INVOICE_DATE]), filter(sales, sales[TYPE] = "actual"), filter (sales, sales[CHANNEL] = blank()))

What i can’t seem to do is to iterate over the max sale price where the condition of invoice_date = max[invoice_date].
pbitest.pbix (120.8 KB)

This model concerns me a little bit. Just by the look of it I can understand why there is a lot of confusion.

Have you gone through this module here…

Setting up your model correctly is how you prevent issues understanding context down the road.

This looks better

But still the multi directional relationships shouldn’t be required.

This is the ideal

I also don’t see a date table. I’m hoping you definitely have this in your real model…

I also recommend creating formulas that more look like the below

image

I cover all of these techniques in the Mastering DAX course module

Also I don’t see any measure groups which I again highly recommend.

Just by reviewing your demo model, the confusion stems from many little things honestly like I’ve detailed above.

If you can make to core/initial things tighter you’ll more easily understand everything post then.

Measure branching is another big tip I think you should look into more.

The ultimate solution was quite easy.

It was the division was always evaluating to one. So then I looked at the underlying formulas and realised they weren’t calculating over the correct columns.

When you break it down it’s the SALES QTY formula that isn’t evaluating correctly. And you can see it when you place it into the context inside the table. It’s no picking up the correct quantity on that last date.

This is ultimately what you need

File here

pbitest.pbix (127.5 KB)

Thanks Sam, i’ll test this out.

I’ll also have a look at the recommended learnings.