# Running Average Product unit cost in Inventory movement table

• ### 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 Friday, July 14, 2017 10:05 AM Formatting
Friday, July 14, 2017 10:03 AM

• Hi Naveen,

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,

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