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

     

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!