Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Seems to be working - but I will do some thorough checks tomorrow.
Many thanks for your help so far, its very much appreciated 😉
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.
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
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?
Yes that's the concept, its only to be viewed on a customer basis, but in both instances it's the results I want
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.
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
Hi - sergej_og, looks like you repsonsed (I have an email), but there is nothing showing in the forume 😞
User | Count |
---|---|
94 | |
86 | |
78 | |
70 | |
63 |
User | Count |
---|---|
114 | |
99 | |
97 | |
65 | |
59 |