cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Leandro Regular Visitor
Regular Visitor

Inventory Average Cost

INVENTORY.PNG

 

 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 most used.

Basically my problem is to calculate the average cost, because the end of a month is the base for the next one, then I find a kind of circular reference in this calculation.

In the image of the to see the formula that changes from line 1 to line 2.

This calculation is really giving me headaches, and I appreciate any help you can give me to solve this problem.

Best Regards.

 

7 REPLIES 7
v-ljerr-msft Super Contributor
Super Contributor

Re: Inventory Average Cost

Hi @Leandro,

 

Could you try the formula below to see if it works in your scenario? Smiley Happy

 

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 Regular Visitor
Regular Visitor

Re: Inventory Average Cost

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 Super Contributor
Super Contributor

Re: Inventory Average Cost

Hi @Leandro,

 

After a few try, I was still not able to figure it out. Smiley Mad 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. Smiley Happy

 

Regards

Leandro Regular Visitor
Regular Visitor

Re: Inventory Average Cost

@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 Frequent Visitor
Frequent Visitor

Re: Inventory Average Cost

Hello Leandro!!

 

In meanwhile do you have you issue solved?

 

I have exactly the same need.

 

Greetings from Portugal.

AnthonyTilley Established Member
Established Member

Re: Inventory Average Cost

i Have created a Pbix file that will do waht your asking for.

it does it across a few colunms that are needed as you will need to do several look ups to refrence back to the previous month.

i have shared this file below

i apologise i did not have time to format the dax correctly and add comments but you should be ablet o follow the logic

PBIX FILE

Highlighted
rsaldanha Frequent Visitor
Frequent Visitor

Re: Inventory Average Cost

Thank you very much for you help and time, AnthonyTilley.

 

I have to analize very carefuly your pbix file. I coulnd't understand the logic at first time I read it.

And I can´t understand yet if it will do with a lot of products.

 

Thanks a lot for your time. I´ll give you feeedback as soon as possible.

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 344 members 3,175 guests
Please welcome our newest community members: