- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Community Information
- Community Accounts & Registration
- Using the Community
- Community Feedback
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- FIFO - Stock Calculation

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

pejczi

Frequent Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-09-2018
12:19 PM

Dear all, I would like to ask you how to calculate stock if the previous row is negative value.

For example :

First photo (on the left) shows my example values. I would like to create a new measure [Stock] which is calculating stock for a year - if the volume left is < 0 in previous row , then the rest is being calculated in the next row, as the example on the 2nd photo shows.

More strict example :

I bought 60 pairs of shoes for 2019. I sold 70 of them, so total volume would be 60-70=-10. I know that I have to reorder next 10 pairs of shoes , I buy additional 30 pairs. My stock in 2nd order is 30-10=20. This is the same as example below.

In those pictures, I am buying some products measured in MWh. I bought 82 MWh on 1. January and sold 500 MWh to clients to clients. I decided to order on 5. January much more, so I bought 8760 MWh. I know that I owed 418 MWh to my customers, so my stock left is 8342.

How to calculate this stock ? Volume left is a measure which is calculated like this :

Volume Left = SUM(Hedge[Volume Hedge [MWh]]])-SUM(Sales[Sales Volume [MWh]]])

where Sales Volume is basically my sales from another table.

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

pejczi

Frequent Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-13-2018
02:03 AM

Okay, I found a solution, maybe it'll help someone :

VolumeStock = CALCULATE ( [Volume Left]; FILTER ( ALL ( Hedge ); 'Hedge'[Hedge Year] = MAX ( 'Hedge'[Hedge Year] ) && Hedge[Hedge Price [zł]]] <= MAX ( Hedge[Hedge Price [zł]]] ) ) )

and then another measure : Volume Stock = IF([VolumeStock]<0;0;[VolumeStock])

3 REPLIES 3

Aron_Moore

Established Member

Re: FIFO - Stock Calculation

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-09-2018
01:17 PM

pejczi

Frequent Visitor

Re: FIFO - Stock Calculation

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-10-2018
04:50 AM

Thank you for your answer.

Apparently, it sums wrong rows - it sums from the volume left from first row of each group, instead of every single from one group until its positive (basing on the article you sent).

VolumeStock = IF ( MIN ( Hedge[Hedge Year] ) <= CALCULATE ( MAX ( Hedge[Hedge Year] ); ALL ( Hedge ) ); CALCULATE([Volume Left];FILTER(ALL(Hedge[Hedge Year]);(Hedge[Hedge Year])<=MAX(Hedge[Hedge Year]))))

pejczi

Frequent Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-13-2018
02:03 AM

Okay, I found a solution, maybe it'll help someone :

VolumeStock = CALCULATE ( [Volume Left]; FILTER ( ALL ( Hedge ); 'Hedge'[Hedge Year] = MAX ( 'Hedge'[Hedge Year] ) && Hedge[Hedge Price [zł]]] <= MAX ( Hedge[Hedge Price [zł]]] ) ) )

and then another measure : Volume Stock = IF([VolumeStock]<0;0;[VolumeStock])