Measure or Column for Last Price Paid

[mattrixdesign2]

I have done some various searches on this, but I cant find answer or missunderstanding it.

I am looking to capture "LAST PRICE BILLED" (at customer / part level).

Example below - all for the same part, this is how I would expect it to look in a calculated column (see last 2 in red)

Part Number	Cust. #	Invoice Date	Unit Price	Customer/Part	Last Billed Date	Last Billed Unit Amount
ABC-DEF-GHI	273658	10/01/2023 00:00	8.88	ABC-DEF-GHI273658	10/01/2023 00:00	8.88
ABC-DEF-GHI	123456	19/12/2022 00:00	7.1	ABC-DEF-GHI123456	19/12/2022 00:00	7.1
ABC-DEF-GHI	123456	29/11/2022 00:00	7.1	ABC-DEF-GHI123456	19/12/2022 00:00	7.1
ABC-DEF-GHI	123456	28/11/2022 00:00	7.1	ABC-DEF-GHI123456	19/12/2022 00:00	7.1
ABC-DEF-GHI	123456	25/11/2022 00:00	7.1	ABC-DEF-GHI123456	19/12/2022 00:00	7.1
ABC-DEF-GHI	123456	24/11/2022 00:00	6.2	ABC-DEF-GHI123456	19/12/2022 00:00	7.1
ABC-DEF-GHI	273658	15/11/2022 00:00	8.88	ABC-DEF-GHI273658	10/01/2023 00:00	8.88
ABC-DEF-GHI	273658	14/11/2022 00:00	8.88	ABC-DEF-GHI273658	10/01/2023 00:00	8.88
ABC-DEF-GHI	123456	06/07/2022 00:00	6.2	ABC-DEF-GHI123456	19/12/2022 00:00	7.1
ABC-DEF-GHI	123456	06/07/2022 00:00	6.2	ABC-DEF-GHI123456	19/12/2022 00:00	7.1
ABC-DEF-GHI	333333	30/06/2022 00:00	11.83	ABC-DEF-GHI333333	30/06/2022 00:00	11.83
ABC-DEF-GHI	123456	28/06/2022 00:00	6.2	ABC-DEF-GHI123456	19/12/2022 00:00	7.1
ABC-DEF-GHI	999999	22/06/2022 00:00	5.6	ABC-DEF-GHI999999	22/06/2022 00:00	5.6
ABC-DEF-GHI	273658	26/01/2022 00:00	4.8	ABC-DEF-GHI273658	10/01/2023 00:00	8.88
ABC-DEF-GHI	151515	20/10/2021 00:00	6.24	ABC-DEF-GHI151515	20/10/2021 00:00	6.24
ABC-DEF-GHI	222222	23/08/2021 00:00	5.67	ABC-DEF-GHI222222	23/08/2021 00:00	5.67
ABC-DEF-GHI	999999	26/03/2021 00:00	6.24	ABC-DEF-GHI999999	22/06/2022 00:00	5.6
ABC-DEF-GHI	151515	20/01/2021 00:00	6.2	ABC-DEF-GHI151515	20/10/2021 00:00	6.24
ABC-DEF-GHI	222222	20/03/2021 00:00	4	ABC-DEF-GHI222222	23/08/2021 00:00	5.67

Ideally I want this to show on a matrix, that will have mutiple transactions, summarised at part level, but in each instance I want to just last billed / and last billed date in 2 columns.

I think a measure would be better. I am also applying filters including calendar dates, customer name etc.

Matt

[mattrixdesign2]

Seems to be working - but I will do some thorough checks tomorrow.

Many thanks for  your help so far, its very much appreciated 😉

[mattrixdesign2]

Thanks I will try this, however I have has a big issue with the report, been working on it for weeks. Went to open it, and all of a sudden lost all my calculations and tables, as if there is no data - the file size is minimum, nothing I did, what makes it worse is its saved this state. Managed to down load a published version from a view days prior, but its set me back as I have some actions to repeat. Then I will get on to trying this out.

[sergej_og]

Ok @mattrixdesign2 ,
then try this out.
You should get (in best case) your target result for last billed price and latest invoice date - with little adjustments to your model.

 - Last price billed

Last price billed = 
 CALCULATE(
     LASTNONBLANKVALUE('Calendar'[Date] , SUM(your_table[unit price]) ),
        SUMMARIZE(
            your_table,
            your_table[Customer],
            your_table[Part No],
            "Date", MAX(your_table[Invoice Date])
            )
 )

 - MAX Invoice date

MAX Inv. Date = 
    MAX(yout_table[Invoice Date])

 
Hope it helps.

Regards
Sergej

[sergej_og]

ok, I think it will work this way.
Just my small dataset...
All customers:

Customer C35:

It looks like your target result.
Am I right?

[mattrixdesign2]

Yes that's the concept, its only to be viewed on a customer basis, but in both instances it's the results I want

[mattrixdesign2]

Does this help - I had trouble with the table I pasted.

I have a matrix all working, I need it at item level, summing up total qty, amount spent etc. I just need 2 columns that show the very last unit price and the date of that unit price. 

[sergej_og]

Hey @mattrixdesign2 ,
I deleted my first suggestion to provide you a solid (maybe better) one.
I tried to recap your request with a small dataset.
Is that your target result/visualization?

Hope I catched it correctly

[mattrixdesign2]

Hi - sergej_og, looks like you repsonsed (I have an email), but there is nothing showing in the forume 😞