D365 Finance & Operations and Dynamics AX Forum

Expand all | Collapse all

Item Cost Price - Power BI Source

  • 1.  Item Cost Price - Power BI Source

    Posted 15 days ago
    Hello everyone, and thank you in advance for any insights you may have!

    In summary, I am trying to create a Power BI Report that shows Items Invoiced as well as respective Cost of those Items.

    Unfortunately the table with Invoice Lines (I'm using SalesInvoiceV2Lines table) does not include the Cost Price of the items sold, just the Sales Price.

    So, I found the table InventItemPrices that has all of our Items' activated costs.  The idea was to bump that up against what was sold so I could include Margin data in the Power BI.

    Unfortunately, the Table lists the Cost Price for each instance where there was a cost update in D365.  So as Cost Updates are periodically performed, each Item will have multiple lines on this table.  You can see this in the attached image: during our Go-Live we had Cost Updates on both 1/24 and 1/25.  In this case the Item Cost Prices are the same, but many are not and I wouldn't expect them to be in the future.

    Yet I can't just filter out on a specific date, because during the course of normal business we have to perform updates on items, new Items are created on intermittent dates, etc.

    Technically, what I'd want to do for each line on the InvoiceLines table would be to pull the Cost Price from this InventItemPrices table that has the date of closest match to when we Shipped/Invoiced.

    I'm not sure if there is perhaps a different table I should instead try to pull from that would simply provide the underlying Standard Cost of those items shipped?  Or if not, if there are any suggestions on how I might go about doing this with formulas or similar?

    InventItemPrices Table


    ------------------------------
    Jeffrey DiOrio
    Vice President of Finance
    The Heico Companies
    Azusa
    ------------------------------


  • 2.  RE: Item Cost Price - Power BI Source

    Posted 14 days ago
    Jeffrey,

    I am still a Power BI newbie, so don't have a solution for you, but a suggestion. The Power BI User Group might be a better resource for you. (https://www.pbiusergroup.com/). I believe that you can use the same login as you did here. They will be able to help more with DAX / G querires you would need.

    Robert

    ------------------------------
    Robert Shannon
    ERP Analyst
    Leatherman Tool Group
    Portland OR
    ------------------------------



  • 3.  RE: Item Cost Price - Power BI Source

    SILVER CONTRIBUTOR
    Posted 14 days ago
    Hello,

    I think you can use a calculated column on your SalesInvoiceV2Line table that will get the price from your InventItemPrices table. Use the lookup function to get the price.The logic should work almost like in excel approximate lookup and it will return the price that's closest to your actual sales date. If not then I am sure you can use a DAX measure to get the info you need. Like they mentioned before, you probably need someone with DAX skills to help you get that measure to work.

    ------------------------------
    Juan Sebastian Grijalba
    Berkowitz Pollack Brant
    Miami
    ------------------------------



If you've found this thread useful, dive deeper into User Group community content by role