Forum Discussion
Inventory Closing Stock
I have been trying to get to find a solution for the below scenario but I couldn't crack it. Hence, I am seeking for help from the community.
Scenario:
I have data below, I need to calculate the Closing Stock based on the previous month's Closing Stock. I am stuck here.
here is the table:
| Category | Mar-22 | Apr-22 | May-22 |
| Production | 4,350 | 4,585 | |
| Supply | 2,254 | 3,324 | |
| Demand | 2,546 | 1,865 | |
| Closing Stock | 2,500 |
Any help is really appreciated.
Hi rocky09 ,
Here are the steps you can follow：
1. Enter the power query, select [Mar-22], [Apr-22], [May-22] – Unpivot Columns.
Result：
2. Select Attirbut - Right click - Change Type - Date/Time.
Result：
3. Create calculated column.
Month = MONTH('Main table'[Attribute])4. Create measure.
Measure = IF( ISINSCOPE('Main table'[Category]),MAX('Main table'[Value]), IF( NOT( ISINSCOPE('Main table'[Category]))&&MAX('Main table'[Month])=MINX(ALL('Main table'),'Main table'[Month]), CALCULATE(SUM('Main table'[Value]),FILTER(ALL('Main table'),'Main table'[Category]="Closing Stock"&&'Main table'[Month]=MINX(ALL('Main table'),'Main table'[Month]))) , CALCULATE(SUM('Main table'[Value]),FILTER(ALL( 'Main table'),'Main table'[Category]="Closing Stock"&&'Main table'[Month]<=MAX('Main table'[Month])-1 )) + CALCULATE(SUM('Main table'[Value]),FILTER(ALL('Main table'),'Main table'[Month]<=MAX('Main table'[Month])&&'Main table'[Category]="Production")) + CALCULATE(SUM('Main table'[Value]),FILTER(ALL('Main table'),'Main table'[Month]<=MAX('Main table'[Month])&&'Main table'[Category]="Supply")) - CALCULATE(SUM('Main table'[Value]),FILTER(ALL('Main table'),'Main table'[Month]<=MAX('Main table'[Month])&&'Main table'[Category]="Demand")) ))5. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
2 Replies
rocky09 First, highly recommend unpivoting your month columns. Then this should be pretty basic. Essentially an MTBF problem. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])
VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
__Current - __PreviousHi rocky09 ,
Here are the steps you can follow：
1. Enter the power query, select [Mar-22], [Apr-22], [May-22] – Unpivot Columns.
Result：
2. Select Attirbut - Right click - Change Type - Date/Time.
Result：
3. Create calculated column.
Month = MONTH('Main table'[Attribute])4. Create measure.
Measure = IF( ISINSCOPE('Main table'[Category]),MAX('Main table'[Value]), IF( NOT( ISINSCOPE('Main table'[Category]))&&MAX('Main table'[Month])=MINX(ALL('Main table'),'Main table'[Month]), CALCULATE(SUM('Main table'[Value]),FILTER(ALL('Main table'),'Main table'[Category]="Closing Stock"&&'Main table'[Month]=MINX(ALL('Main table'),'Main table'[Month]))) , CALCULATE(SUM('Main table'[Value]),FILTER(ALL( 'Main table'),'Main table'[Category]="Closing Stock"&&'Main table'[Month]<=MAX('Main table'[Month])-1 )) + CALCULATE(SUM('Main table'[Value]),FILTER(ALL('Main table'),'Main table'[Month]<=MAX('Main table'[Month])&&'Main table'[Category]="Production")) + CALCULATE(SUM('Main table'[Value]),FILTER(ALL('Main table'),'Main table'[Month]<=MAX('Main table'[Month])&&'Main table'[Category]="Supply")) - CALCULATE(SUM('Main table'[Value]),FILTER(ALL('Main table'),'Main table'[Month]<=MAX('Main table'[Month])&&'Main table'[Category]="Demand")) ))5. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
