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 Mar 18, 2020 09:56 AM
    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
    ------------------------------
    Academy - Online Interactive Learning from Experts


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

    Posted Mar 19, 2020 12:43 PM
    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
    ------------------------------

    Academy - Online Interactive Learning from Experts


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

    SILVER CONTRIBUTOR
    Posted Mar 19, 2020 02:06 PM
    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
    ------------------------------

    Academy - Online Interactive Learning from Experts


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

    Posted Apr 07, 2020 07:49 AM
    Hi everyone.  I was just thinking about this and was thinking that perhaps I'm trying to make the solution more complicated than it needs to be.

    Let me ask a different question: Is there a Power BI Table that would provide me the same information that is in the canned Gross Margin by Customer report?

    That's really what I'm trying to do, simply pull a table from within Power BI that provides the same information as the Gross Margin by Customer report.  But since I haven't been able to find that, I came up with the rather convoluted solution noted in my first post :(

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

    Academy - Online Interactive Learning from Experts


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

    SILVER CONTRIBUTOR
    Posted Apr 07, 2020 08:35 AM
      |   view attached
    Hello,

    To my knowledge there isn't a data entity that will give you gross margin by customer easily out of the box. There is an aggregate data measurement (Fancy name for a data cube) called sales cube. Here is the caveat you can't access that data directly from powerbi on your desktop. You need to access that data from a developer's environment using powerbi. You can modify the report in the developer's environment and then embedded the report into D365 through a deployment. Here is the report that will give you that data from Life cycle services. Curious to know if anybody else will have other solutions

    Regards,

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

    Academy - Online Interactive Learning from Experts


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

    TOP CONTRIBUTOR
    Posted Apr 09, 2020 03:53 AM
    try data entity  and suggest
    Pending and active item prices


    ------------------------------
    Regards
    Rahul Mohta
    Advisor - D365FO
    Real Dynamics
    ------------------------------

    Academy - Online Interactive Learning from Experts


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