locked
Running Average Product unit cost in Inventory movement table RRS feed

  • Question

  • 

    In an inventory movement table i need to create an average unit cost column which is a result of first in first out inventory method. I am using product and date table in my Power Pivot model. Any help writing DAX formula is highly appreciated.

    Date Type Product Qty Unit Rate Required Result Running Average unit Cost 
    01/01/2017 Purchase A 5 100  100.00 
    01/01/2017 Purchase B 5 200  200.00 
    05/01/2017 Sales A -1 200  100.00 
    05/01/2017 Sales B -1 400  200.00 
    06/01/2017 Sales A -2 200  100.00 
    06/01/2017 Sales B -2 400  200.00 
    08/01/2017 Sales A -1 180  100.00 
    08/01/2017 Sales B -1 360  200.00 
    09/01/2017 Purchase A 5 110  108.33 
    09/01/2017 Purchase B 5 180  183.33 
    10/01/2017 Sales A -4 210  108.33 
    10/01/2017 Sales B -4 210  183.33 
    12/01/2017 Sales A -2 200  108.33 
    12/01/2017 Sales B -2 200  183.33 
    13/01/2017 Purchase A 4 105  105.00 
    13/01/2017 Purchase B 4 190  190.00 
    15/01/2017 Sales A -2 200  105.00 
    15/01/2017 Sales B -2 200  190.00 
    16/01/2017 Purchase A 3 120  114.00 
    16/01/2017 Sales A -2 200  114.00 
    16/01/2017 Purchase B 3 195  193.00 
    16/01/2017 Sales B -2 200  193.00 
    17/01/2017 Sales A -2 200  114.00 
    17/01/2017 Sales B -2 200  193.00 

    • Edited by naveenajmera Friday, July 14, 2017 10:05 AM Formatting
    Friday, July 14, 2017 10:03 AM

Answers

  • Hi Naveen,

    Thanks for your question.

    Following are the steps to achieve your requirements.

    Create a calculated column called RuningBalance as below:

    RuningBalance :=
    CALCULATE (
        SUM ( tab[Qty] ),
        FILTER (
            tab,
            tab[Product] = EARLIER ( tab[Product] )
                && tab[Date] <= EARLIER ( tab[Date] )
        )
    )

    Create a calculated column called Rate as below:
    Rate :=
    VAR rate =
        CALCULATE (
            MAX ( tab[UnitRate] ),
            FILTER (
                tab,
                tab[Type] = "Purchase"
                    && tab[Product] = EARLIER ( tab[Product] )
                    && tab[Date] <= EARLIER ( tab[Date] )
            )
        )
    RETURN
        IF ( tab[Type] = "Purchase", tab[Qty] * tab[UnitRate], tab[Qty] * rate )

    Create a calculated column called TotalRate as below:
    TotalRate :=
    CALCULATE (
        SUM ( tab[Rate] ),
        FILTER (
            tab,
            tab[Product] = EARLIER ( tab[Product] )
                && tab[Date] <= EARLIER ( tab[Date] )
        )
    )

    Create a calculated column called result as below:
    result := If(tab[RuningBalance]=0 && tab[TotalRate] <=0,0,tab[TotalRate]/tab[RuningBalance])


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, July 17, 2017 7:37 AM

All replies

  • are you want select query to Result Running Average unit Cost ?

    Dev M.Basha

    Friday, July 14, 2017 11:47 AM
  • No, my table have first 5 columns in data. The last column is desired. I am looking for a DAX solution. 
    Friday, July 14, 2017 2:54 PM
  • There is not enough information here. Average over what?

    For example, how did you come up with 108.33 below?

    09/01/2017 Purchase A 5 110  108.33 
    Friday, July 14, 2017 3:23 PM
  • Its running average of purchase cost based on first in first out. Looking for DAX solution tho this. Excel calculation is like this

    Date Type Product Qty Unit Rate Running Balance PurchaseCost Total Rate DESIRED Running Average unit Cost 
    01/01/2017 Purchase A 5 100 5 500  500.00   100.00 
    05/01/2017 Sales A -1 200 4 0  400.00   100.00 
    06/01/2017 Sales A -2 200 2 0  200.00   100.00 
    08/01/2017 Sales A -1 180 1 0  100.00   100.00 
    09/01/2017 Purchase A 5 110 6 550  650.00   108.33 
    10/01/2017 Sales A -4 210 2 0  216.67   108.33 
    12/01/2017 Sales A -2 200 0 0  -     108.33 
    13/01/2017 Purchase A 4 105 4 420  420.00   105.00 
    15/01/2017 Sales A -2 200 2 0  210.00   105.00 
    16/01/2017 Purchase A 3 120 5 360  570.00   114.00 
    16/01/2017 Sales A -2 200 3 0  342.00   114.00 
    17/01/2017 Sales A -2 200 1 0  114.00   114.00 

    Friday, July 14, 2017 6:04 PM
  • Hello,

    Here is  the measure you need

    Measure :=
    SUMX ( Table1, 
        DIVIDE ( Table1[TotalRate], Table1[Running Balance] ) 
     )

    Nick -

    Friday, July 14, 2017 6:39 PM
  • Hi Nick

    It does not give correct result on the date 16/01/2017. On this date there are multiple transactions.

    Thanks

    Naveen

    Friday, July 14, 2017 8:01 PM
  •  

    I see now. In that case use AVERAGEX instead of SUMX.
    It will work now.

    Measure:=
     AVERAGEX(
       Table1, 
       DIVIDE(
          Table1[Total Rate],  
          Table1[Running Balance])
     )
    


    Nick -

    Friday, July 14, 2017 10:20 PM
  • Hi Naveen,

    Thanks for your question.

    Following are the steps to achieve your requirements.

    Create a calculated column called RuningBalance as below:

    RuningBalance :=
    CALCULATE (
        SUM ( tab[Qty] ),
        FILTER (
            tab,
            tab[Product] = EARLIER ( tab[Product] )
                && tab[Date] <= EARLIER ( tab[Date] )
        )
    )

    Create a calculated column called Rate as below:
    Rate :=
    VAR rate =
        CALCULATE (
            MAX ( tab[UnitRate] ),
            FILTER (
                tab,
                tab[Type] = "Purchase"
                    && tab[Product] = EARLIER ( tab[Product] )
                    && tab[Date] <= EARLIER ( tab[Date] )
            )
        )
    RETURN
        IF ( tab[Type] = "Purchase", tab[Qty] * tab[UnitRate], tab[Qty] * rate )

    Create a calculated column called TotalRate as below:
    TotalRate :=
    CALCULATE (
        SUM ( tab[Rate] ),
        FILTER (
            tab,
            tab[Product] = EARLIER ( tab[Product] )
                && tab[Date] <= EARLIER ( tab[Date] )
        )
    )

    Create a calculated column called result as below:
    result := If(tab[RuningBalance]=0 && tab[TotalRate] <=0,0,tab[TotalRate]/tab[RuningBalance])


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, July 17, 2017 7:37 AM
  • Please verify that the numerical values of my example match your values.
    Please ignore all the incomprehensible formulas.
    No DAX yet.
    http://www.mediafire.com/file/oqges131se44gec/07_15_17.xlsx
    http://www.mediafire.com/file/twhq3b7bs5c8bdp/07_15_17.pdf

    Wednesday, July 19, 2017 2:04 AM
  •  Herbert,

    Looking at your excel solution got me a bit melancholic and nostalgic about the the pre-DAX good old days when excel functions ruled the planet earth....:)  I am exaggerating,  Excel still remains and most likely will always remain number one analysis tool.

    I especially enjoyed the sequencing helper column logic. With your permission I am stealing this trick.

    =COUNTIFS(INDEX([Prod],1) : [@Prod],[@Prod])

    Wednesday, July 19, 2017 4:15 AM
  • Nick,
    Graciously granted.
    Also, Excel functions are better at circular dependencies.
    For PP restrictions, see pp 225-229 of the book
    "Building Data Models with PowerPivot"
    by Ferrari and Russo.
    There are oodles of circular dependencies in this example.

    Wednesday, July 19, 2017 4:54 PM