Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Latest purchase price

Hi
Can anyone help me to get the latest purchase price based on date.

In the VendInvoiceTrans i have a date column but i only wanna see the purchase price of the last puchase order based on the latest date. My measure seems to sum all purchase prices for all orders.

 

 

 

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi Anonymous ,

    According to your description, try to modify the formula like this:

    Last PP unit =
VAR MaxDate =
    CALCULATE (
        MAX ( 'VendInvoiceTrans'[INVOICEDATE] ),
        ALLEXCEPT ( 'VendInvoiceTrans', 'VendInvoiceTrans'[ITEMID] )
    )
RETURN
    SUMX (
        FILTER ( 'VendInvoiceTrans', 'VendInvoiceTrans'[INVOICEDATE] = MaxDate ),
        DIVIDE ( '00 measure'[Purchase Amount DKK], 'VendInvoiceTrans'[QTY] )
    )

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • aj1973's avatar
    aj1973
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi Anonymous 

    Seems like you used too many filter context in your measure.

    Can you share a sample of your PBIX?

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Anonymous ,

    According to your description, try to modify the formula like this:

    Last PP unit =
VAR MaxDate =
    CALCULATE (
        MAX ( 'VendInvoiceTrans'[INVOICEDATE] ),
        ALLEXCEPT ( 'VendInvoiceTrans', 'VendInvoiceTrans'[ITEMID] )
    )
RETURN
    SUMX (
        FILTER ( 'VendInvoiceTrans', 'VendInvoiceTrans'[INVOICEDATE] = MaxDate ),
        DIVIDE ( '00 measure'[Purchase Amount DKK], 'VendInvoiceTrans'[QTY] )
    )

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.