Forum Discussion

Omar3000's avatar
Omar3000
Regular Visitor
8 years ago
Solved

Inventory turnover by month

Hello everyone!

 

I hope anyone can help me with the next issue:

 

I want to know the inventory turnonver of each product I have by month.

 

I have the next tables:

 

MULT, in wich i have the details of each product with the next relevants:

  • CVE_ART, this is the product,
  • EXIST, this is the product quantity,
  • INVE. ACT., this is the cost of the product at the moment.

MINVE, in wich I have all movements of each product with the next relevants:

  • M.COST.2017, this is the total cost of the movement.
  • SIGNO, this tells what is an input and output movement.

 

I need to creat a measure of M.COST.2017 and substract to INVE.ACT to get the inicial inventory of each prodcut,

Then I have to get the final inventory for each product by month,

Then I have to get all the outputs in cost for each product by month and aplicate the next formula:

 

Turnover by month

 

January:   Total cost outputs for january / average inventory (January)

February:  Total cost outputs for february / average inventory (January + Febreuary / 2)

March:     Total cost outputs for march / average inventory (January + February + March / 3)

... and the same for each month

 

I hope somebody can help me with this problem I have.

 

Thank you all for helping.

 

Omar,

  • Eric_Zhang's avatar
    Eric_Zhang
    8 years ago

    Omar3000

    You can try a measure as

    turn over value =
DIVIDE (
    SUMX (
        FILTER ( ALLSELECTED ( yourTable ), yourTable[date] <= MAX ( yourTable[date] ) ),
        yourTable[value]
    ),
    CALCULATE (
        DISTINCTCOUNT ( yourTable[month] ),
        FILTER ( ALLSELECTED ( yourTable ), yourTable[date] <= MAX ( yourTable[date] ) )
    )
)

    If it is not your case, please post some sample data and expected output.

1 Reply

  • Eric_Zhang's avatar
    Eric_Zhang
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Omar3000

    You can try a measure as

    turn over value =
DIVIDE (
    SUMX (
        FILTER ( ALLSELECTED ( yourTable ), yourTable[date] <= MAX ( yourTable[date] ) ),
        yourTable[value]
    ),
    CALCULATE (
        DISTINCTCOUNT ( yourTable[month] ),
        FILTER ( ALLSELECTED ( yourTable ), yourTable[date] <= MAX ( yourTable[date] ) )
    )
)

    If it is not your case, please post some sample data and expected output.