Forum Discussion

mattrixdesign2's avatar
mattrixdesign2
Icon for Helper I rankHelper I
3 years ago

Measure or Column for Last Price Paid

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 NumberCust. #Invoice DateUnit PriceCustomer/PartLast Billed DateLast Billed Unit Amount
ABC-DEF-GHI27365810/01/2023 00:008.88ABC-DEF-GHI27365810/01/2023 00:008.88
ABC-DEF-GHI12345619/12/2022 00:007.1ABC-DEF-GHI12345619/12/2022 00:007.1
ABC-DEF-GHI12345629/11/2022 00:007.1ABC-DEF-GHI12345619/12/2022 00:007.1
ABC-DEF-GHI12345628/11/2022 00:007.1ABC-DEF-GHI12345619/12/2022 00:007.1
ABC-DEF-GHI12345625/11/2022 00:007.1ABC-DEF-GHI12345619/12/2022 00:007.1
ABC-DEF-GHI12345624/11/2022 00:006.2ABC-DEF-GHI12345619/12/2022 00:007.1
ABC-DEF-GHI27365815/11/2022 00:008.88ABC-DEF-GHI27365810/01/2023 00:008.88
ABC-DEF-GHI27365814/11/2022 00:008.88ABC-DEF-GHI27365810/01/2023 00:008.88
ABC-DEF-GHI12345606/07/2022 00:006.2ABC-DEF-GHI12345619/12/2022 00:007.1
ABC-DEF-GHI12345606/07/2022 00:006.2ABC-DEF-GHI12345619/12/2022 00:007.1
ABC-DEF-GHI33333330/06/2022 00:0011.83ABC-DEF-GHI33333330/06/2022 00:0011.83
ABC-DEF-GHI12345628/06/2022 00:006.2ABC-DEF-GHI12345619/12/2022 00:007.1
ABC-DEF-GHI99999922/06/2022 00:005.6ABC-DEF-GHI99999922/06/2022 00:005.6
ABC-DEF-GHI27365826/01/2022 00:004.8ABC-DEF-GHI27365810/01/2023 00:008.88
ABC-DEF-GHI15151520/10/2021 00:006.24ABC-DEF-GHI15151520/10/2021 00:006.24
ABC-DEF-GHI22222223/08/2021 00:005.67ABC-DEF-GHI22222223/08/2021 00:005.67
ABC-DEF-GHI99999926/03/2021 00:006.24ABC-DEF-GHI99999922/06/2022 00:005.6
ABC-DEF-GHI15151520/01/2021 00:006.2ABC-DEF-GHI15151520/10/2021 00:006.24
ABC-DEF-GHI22222220/03/2021 00:004ABC-DEF-GHI22222223/08/2021 00:005.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

 

Need Help

8 Replies

  • sergej_og's avatar
    sergej_og
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
      mattrixdesign2
      Icon for Helper I rankHelper I
      3 years ago

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

  • sergej_og's avatar
    sergej_og
    Icon for Super User rankSuper User
    3 years ago

    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

  • mattrixdesign2's avatar
    mattrixdesign2
    Icon for Helper I rankHelper I
    3 years ago

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

  • sergej_og's avatar
    sergej_og
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
    mattrixdesign2
    Icon for Helper I rankHelper I
    3 years ago

    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. 

  • mattrixdesign2's avatar
    mattrixdesign2
    Icon for Helper I rankHelper I
    3 years ago

    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.

  • mattrixdesign2's avatar
    mattrixdesign2
    Icon for Helper I rankHelper I
    3 years ago

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

     

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