Forum Discussion

Leandro's avatar
Leandro
Icon for Advocate I rankAdvocate I
9 years ago

Inventory Average Cost

   Hello guys, I am having difficulty in a calculation in DAX, the calculation is of weighted average cost of stock, here in Brazil we have many variations of cost, then this method is the ...
Need Help
v-ljerr-msft's avatar
v-ljerr-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Hi Leandro,

 

Could you try the formula below to see if it works in your scenario? :smileyhappy:

 

Average Cost = 
VAR currentPeriod = Table1[Period]
VAR minPeriod =
    CALCULATE ( MIN ( Table1[Period] ), ALL ( Table1 ) )
RETURN
    IF (
        currentPeriod = minPeriod,
        DIVIDE (
            Table1[Incial Total Value] + Table1[Input Total Value],
            Table1[Incial Quant] + Table1[Input Quant]
        ),
        DIVIDE (
            CALCULATE (
                SUMX ( Table1, Table1[Incial Total Value] + Table1[Input Total Value] ),
                FILTER ( ALL ( Table1 ), Table1[Period] < currentPeriod )
            )
                + Table1[Input Total Value],
            CALCULATE (
                SUMX ( Table1, Table1[Incial Quant] + Table1[Input Quant] + Table1[Out Quant] ),
                FILTER ( ALL ( Table1 ), Table1[Period] < currentPeriod )
            )
                + Table1[Input Quant]
        )
    )

 

Regards

Leandro's avatar
Leandro
Icon for Advocate I rankAdvocate I
9 years ago

Hi v-ljerr-msft 

Thanks a lot for your help, i tried the formula that you sent, but there is something that still not working, but i can see the metod is the right, setting "VAR" for the calculation to avoid circular relationships.

Maybe my description of the problem was not precise enough, so i posted the file on google drive, this way it can light the problem.

And again, thanks so much for the help, it will really make my job a lot easier.


EXCEL FILE

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi Leandro,

     

    After a few try, I was still not able to figure it out. :smileymad: So I did some research, then it turns out that it may be not possible to do it with DAX in this scenario, because of the circular dependency. Here is the similar thread for your reference. :smileyhappy:

     

    Regards

    • Leandro's avatar
      Leandro
      Icon for Advocate I rankAdvocate I
      9 years ago

      v-ljerr-msft Thanks a lot for your atention on this issue, seems like you're right, if in the future if a find some way to solve this, i'll let you know!

      Thanks for your help!
      Regards!

      • rsaldanha's avatar
        rsaldanha
        Frequent Visitor
        7 years ago

        Hello Leandro!!

         

        In meanwhile do you have you issue solved?

         

        I have exactly the same need.

         

        Greetings from Portugal.