Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
nileshp
Regular Visitor

Inventory valuation

Hi,

 

I am not able to figure out a measure for the following problem.

 

there are 2 tables, inventory balance showing item wise inventory balance qty as on say, 31-Mar-2018. Second table is purchase register which has column of item name, purchase date, qty, rate and amount.

 

I want a measure to calculate value of inventory balance by applying the rate of latest purchase. if X item has balance of 100 and in purchase register X if purchased as follows:

Date,Qty,Rate,Amt

1-Jan-2018,50,100,5000

2-Feb-2018,70,110,7700

5-Mar-2018,40,90,3600

31-Mar-2018,50,80,4000

 

then value of 100 units of X will be 8700 (50x80+40x90+10x110)

 

Pl help

 

Nilesh

1 ACCEPTED SOLUTION

Hi Nilesh,

 

Please check out the demo in the attachment.

1. I'm afraid we need to modify the relationship first. It's the items that have balance QTY rather than the dates.

Inventory_valuation

2. We can allocate the QTY first. So we add a calculated column like this.

allocation =
VAR currentItem = [Item Name]
VAR currentDate = [Date]
VAR maxDate =
    RELATED ( Stock[Date] )
VAR totalQty =
    RELATED ( Stock[Qty] )
VAR cumulation =
    CALCULATE (
        SUM ( PurReg[Qty] ),
        FILTER (
            ALLEXCEPT ( PurReg, PurReg[Item Name] ),
            'PurReg'[Date] >= currentDate
                && PurReg[Date] <= maxDate
        )
    )
RETURN
    IF (
        ISBLANK ( cumulation ),
        0,
        IF (
            cumulation <= totalQty,
            [Qty],
            IF ( cumulation - [Qty] < totalQty, totalQty - ( cumulation - [Qty] ), 0 )
        )
    )

3. Finally, the measure could be simple.

Measure =
SUMX ( 'PurReg', 'PurReg'[allocation] * 'PurReg'[Rate] )

 

Best Regards,
Dale

 

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

View solution in original post

4 REPLIES 4
Phil_Seamark
Employee
Employee

HI @nileshp

 

Do you have some sample data showing your two tables (inventory and purchase)?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

 

simple model with test data added

https://www.dropbox.com/s/yd2bvhha0srpd6k/stock%20value.pbix?dl=0

 

Nilesh

Hi Nilesh,

 

Please check out the demo in the attachment.

1. I'm afraid we need to modify the relationship first. It's the items that have balance QTY rather than the dates.

Inventory_valuation

2. We can allocate the QTY first. So we add a calculated column like this.

allocation =
VAR currentItem = [Item Name]
VAR currentDate = [Date]
VAR maxDate =
    RELATED ( Stock[Date] )
VAR totalQty =
    RELATED ( Stock[Qty] )
VAR cumulation =
    CALCULATE (
        SUM ( PurReg[Qty] ),
        FILTER (
            ALLEXCEPT ( PurReg, PurReg[Item Name] ),
            'PurReg'[Date] >= currentDate
                && PurReg[Date] <= maxDate
        )
    )
RETURN
    IF (
        ISBLANK ( cumulation ),
        0,
        IF (
            cumulation <= totalQty,
            [Qty],
            IF ( cumulation - [Qty] < totalQty, totalQty - ( cumulation - [Qty] ), 0 )
        )
    )

3. Finally, the measure could be simple.

Measure =
SUMX ( 'PurReg', 'PurReg'[allocation] * 'PurReg'[Rate] )

 

Best Regards,
Dale

 

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

Thanks. Sorry for the late reply. 

 

This solution works. still trying to understand the formula.

However, in actual model, stock table will have qty balance as on difference dates.. Say A is 1000 as on 31-Mar. If I add row A is 400 on 28-Feb, it will not work. I need to create a separate item table since item name is repeated in stock table.

No idea what to do after that. i think calcuated column may not work.

 

thanks

nilesh

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.