Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Omar3000
Regular Visitor

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,

1 ACCEPTED SOLUTION
Eric_Zhang
Microsoft Employee
Microsoft Employee

@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] ) )
    )
)

Capture.PNG

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

View solution in original post

1 REPLY 1
Eric_Zhang
Microsoft Employee
Microsoft Employee

@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] ) )
    )
)

Capture.PNG

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.