Return Latest Price for Multiple Lines

[Anonymous]

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.  😞  

[luchitogiuse]

@v-alq-msft THANKSS!!  i've been trying for days! 

It helped me a lot! thanks for your help! 

[v-alq-msft · Accepted solution]

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]

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]

@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!