Inventory list value from prices

[SvenvandenHil]

Hi,

We have an inventory list and for each item you can see how much the inventory changes per date and location.
I already made a PBI for the quantity of each item on specific dates, but next to that we want to see the value of every item on a specific date. 

I have a table with all prices per item, but every price haves an activation date. So if I need to calculate the value of the inventory on a specific date I need the price of the activation date. But there aren't any end dates in this price table. So only the start date.

Next to that there are some more thing which should be considered in the calculation.

• An Item can be on multiple locations, but for every location we could have other prices, for now we only have 2 locations (1 and 2) The locations are also in the InventoryTransactions table
• An item has a PriceUnit, so the price is per 1 or per 5 or per 1000. So if the inventory changes with 500 units and the price is per 1000 this should be calculated correctly
• There is also a PriceType. An item could have 2 PriceTypes (0 and 1) If so PriceType 1 is leading. But if 1 isn't available the calculation should be done with PriceType 0.

And at last there is the ActivationDate, the calculations for the value of the inventory should be done on the price that's active in the chosen period. As an example you have item 10002848 and it's price with activation date 1-1-2023 is 1,00 per item. And I have the same item priced on activation date 1-1-2024 for 1,05 per item. All transactions done in 2023 should be for the price of 1,00 per item. So if I filter on date 30-06-2023 I should see all inventory changes untill that date for 1,00 per item. But if I filter until 30-06-2024 than all inventory changes should be against the 1,05 per item. So also the changes in 2023. So the filter should look up the value which is active in that same period and should calculate all inventory transactions with that value.

Please see below links to example files of the transactions and price tables

Pbix file 
xlsx file 

[Anonymous · Accepted solution]

Hi, @SvenvandenHil 

Regarding the issue you raised, my solution is as follows:

1.First, I created a time calculation table as a slicer:

Time = 
CALENDAR (
    MIN (
        MIN ( 'InventItemPriceStaging'[ACTIVATIONDATE] ),
        MIN ( 'MainInventoryTransactions'[Inventory Physical Date] )
    ),
    MAX (
        MAX ( 'MainInventoryTransactions'[Inventory Physical Date] ),
        MAX ( 'MainInventoryTransactions'[Inventory Physical Date] )
    )
)

2. Below are the measure I've created for your needs：

MEASURE = 
VAR select1 =
    MAX ( 'Time'[Date] )
VAR type1 =
    CALCULATE (
        MAX ( 'InventItemPriceStaging'[PRICETYPE] ),
        FILTER (
            ALLEXCEPT (
                'InventItemPriceStaging',
                'InventItemPriceStaging'[ITEMID],
                'InventItemPriceStaging'[LOCATION]
            ),
            'InventItemPriceStaging'[ACTIVATIONDATE] <= select1
        )
    )
VAR unit1 =
    CALCULATE (
        MAX ( 'InventItemPriceStaging'[PRICEUNIT] ),
        ALLEXCEPT (
            InventItemPriceStaging,
            'InventItemPriceStaging'[ITEMID],
            'InventItemPriceStaging'[LOCATION]
        )
    )
VAR price1 =
    CALCULATE (
        MAX ( 'InventItemPriceStaging'[PRICE] ),
        FILTER (
            ALLEXCEPT (
                'InventItemPriceStaging',
                'InventItemPriceStaging'[ITEMID],
                'InventItemPriceStaging'[LOCATION]
            ),
            'InventItemPriceStaging'[ACTIVATIONDATE] <= select1
                && 'InventItemPriceStaging'[PRICETYPE] = type1
        )
    )
VAR price2 = price1 / unit1
VAR cid =
    MAX ( 'InventItemPriceStaging'[ITEMID] )
VAR cl =
    MAX ( 'InventItemPriceStaging'[LOCATION] )
VAR inventor =
    CALCULATE (
        SUM ( MainInventoryTransactions[Quantity Inventory Unit] ),
        FILTER (
            'MainInventoryTransactions',
            'MainInventoryTransactions'[Inventory Physical Date] <= select1
                && 'MainInventoryTransactions'[ITEMID] = cid
                && 'MainInventoryTransactions'[LOCATION] = cl
        )
    )
RETURN
    inventor * price2

3.Here's my final result, which I hope meets your requirements.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

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

[SvenvandenHil]

Hi Leroy Lu,

The formula still works like a charm, but as expected I can't seem to get totals. This is due to that the formula is searching the MAX price belonging to the itemID, only the total row doesn't have an itemID which makes that the DAX formula just chooses the MAX price from the whole table and calculate with that price. Do you know any solution or work around for getting the correct totals?

[Anonymous]

Hi,@SvenvandenHil 
Regarding the issue you raised, my solution is as follows:
 

In response to your question, I offer two approaches:
 

1.The first is to use the HASONEFILTER () function as the basis for judgment, and then output the SUMX () function as the aggregate.
 

Here are the related links, I hope you will be helpful:

Dealing with Measure Totals - Microsoft Fabric Community
Measure Totals, The Final Word - Microsoft Fabric Community
 

2.The second is to convert the measures into groups of calculations and then modify the aggregation:
 

You can try the following code as a calculated column:

Then modify the way it aggregates:

Best Regards,

Leroy Lu

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

[SvenvandenHil]

Hi Leroy,

Do you have an example for one or both of these solutions?

Kind regards,

Sven

[SvenvandenHil]

Hi Leroy Lu,

Measure works great. On a few items I don't get the reponse as expected, but that's due to something in the dataset. Thank you for your help.