Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Return Latest Price for Multiple Lines

Hello all!  First off, thanks in advance for any help anyone can provide, it is greatly appreciated!

 

I have done a lot of searching, and can't quite find anything that I can get to work.  As someone who is very much a Power BI novice, I'm certain that the problem lies with me, but if anyone can walk me through a solution it would be absolutely wonderful.

 

Here's the situation:

 

I have 2 tables that I am pulling from D365: 'InventItemPrices' and 'InventorySitesOn-Hand'.  The 'InventorySitesOn-Hand' table provides [ItemNumber] as well as the [OnHandQuantity], but no Value for the on-hand quantity.  While 'InventItemPrices' provides [Price] for each [ItemNumber].  However each [ItemNumber] is shown multiple times, based on the [PriceCreatedDateTime] that the [Price] was updated in the system.

 

So for example, in the 'InventItemPrices' table, it would show [ItemNumber] X00123456 5 times, each time with a different [PriceCreatedDateTime].

 

Whether it's in the 'InventItemPrices' table or the 'InventorySitesOn-Hand' table, I'm trying to be able to have just the [Price] for the latest [PriceCreatedDateTime] so that I can then multiply it by the [OnHandQuantity] and get the actual on-hand value.

 

I'm trying to do this creating a new column instead of a measure, only because the resources I was able to find used that as the solution.  However after doing a lot of searching, I was able to only get as far as getting a column that yields the last [PriceCreatedDateTime] but not the actual Value per [ItemNumber] corresponding to that last date.  😞  

Need Help
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, Anonymous 

     

    Based on your description, I created data to reproduce your scenario.

    InventorySitesOn-Hand:

     

    InventItemPrices:

     

    There is many-to-one relationship between 'InventItemPrices' and 'InventorySitesOn-Hand' tables.

     

    You may create a calculated column in InventorySitesOn-Hand as below.

     

    Result = 
var _itemnum = 'InventorySitesOn-Hand'[ItemNumber]
var _latestdatetime = 
CALCULATE(
    MAX(InventItemPrices[PriceCreatedDateTime]),
    FILTER(
        ALL(InventItemPrices),
        InventItemPrices[ItemNumber] = _itemnum
    )
)
var _price = 
CALCULATE(
    SUM(InventItemPrices[Price]),
    FILTER(
        ALL(InventItemPrices),
        InventItemPrices[PriceCreatedDateTime] = _latestdatetime&&
        InventItemPrices[ItemNumber] = _itemnum
    )
)
return
[OnHandQuantity]*_price

     

     

    Result:

     

    Best Regards

    Allan

     

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

     

4 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi, Anonymous 

     

    Based on your description, I created data to reproduce your scenario.

    InventorySitesOn-Hand:

     

    InventItemPrices:

     

    There is many-to-one relationship between 'InventItemPrices' and 'InventorySitesOn-Hand' tables.

     

    You may create a calculated column in InventorySitesOn-Hand as below.

     

    Result = 
var _itemnum = 'InventorySitesOn-Hand'[ItemNumber]
var _latestdatetime = 
CALCULATE(
    MAX(InventItemPrices[PriceCreatedDateTime]),
    FILTER(
        ALL(InventItemPrices),
        InventItemPrices[ItemNumber] = _itemnum
    )
)
var _price = 
CALCULATE(
    SUM(InventItemPrices[Price]),
    FILTER(
        ALL(InventItemPrices),
        InventItemPrices[PriceCreatedDateTime] = _latestdatetime&&
        InventItemPrices[ItemNumber] = _itemnum
    )
)
return
[OnHandQuantity]*_price

     

     

    Result:

     

    Best Regards

    Allan

     

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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Dear v-alq-msft

       

      I am trying to apply the formula you posted here to a very similar dataset to the one you created in your post. The only difference is that I have more than one entry in InventorySitesOn-Hand table for each itemNumber. I mean I have the closing balance inventory for each item for each month end, so my table looks like the one below:

      I have change the field of your formula with the corrsponding field in my dataset but an issue with the var -lastestdatetime appears and I cant not figure out how to solve it. 

      Below the formula of my dataset:

       

       

      inv_value = 
var _itemnum = Inventarios[art]
var _latestdatetime = 
CALCULATE(
    MAX(ARTIMP[costo_unitario_usd];
    FILTER(
        ALL(ARTIMP);
        ARTIMP[art] = _itemnum
    )
)
var _price = 
CALCULATE(
    SUM(ARTIMP[costo_unitario_usd]);
    FILTER(
        ALL(ARTIMP);
        ARTIMP[fecha] = _latestdatetime&&  ARTIMP[art] = _itemnum
        
    )
)
return
[cant]*_price​<p>  I will appreciate very much your help to solve this issue.</p><p>Best regrads,</p><p>Andrés</p>

       

      v-alq-msft 

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      v-alq-msft Allan - thank you SOOOOO much for your help!  I had been spinning my wheels for days trying to research this and figure this out to no-avail.  I am very much appreciative!