Forum Discussion

SvenvandenHil's avatar
SvenvandenHil
Frequent Visitor
2 years ago
Solved

Inventory list value from prices

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...
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
SvenvandenHil
Frequent Visitor
2 years ago

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's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      SvenvandenHil
      Frequent Visitor
      2 years ago

      Hi Leroy,

       

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

      Kind regards,

       

      Sven