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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Employee
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
Employee
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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors