cancel
Showing results for
Did you mean:
Frequent Visitor

## Help for Calculation

Hello Friends,

I had the following calculated tables from raw data.

I want to calculate

Sept' 21 . The closing stock for Sept'21 = Stock + Receipt of Sept - Consumption of Sept

For Oct'21.: Opening Stock of Oct'21= Closing stock of Sept'21,

Closing stock of Oct'21 = Opening stock of Oct'21 + Receipt of Oct'21 - Consumption of Oct'21.

For Nov'21 : Opening Stock of Nov'21= Closing stock of Oct'21,

Closing stock of NOv.'21 = Opening stock of Nov'21 + Receipt of Nov'21 - Consumption of Oct'21.

And so on....

Will be grateful to you for providing the solution.

1 ACCEPTED SOLUTION
Community Support

Hi @Narayan ,

You can try this measure:

``````A =
VAR totalConsumption =
CALCULATE (
SUM ( 'Consumption'[Consumption] ),
FILTER (
ALLSELECTED ( 'Consumption'[Date].[Date] ),
[Date].[Date] <= MAX ( [Date].[Date] )
)
)
VAR totalstock =
SUM ( 'Stock'[Stock] )
VAR totalreceipt =
CALCULATE (
SUM ( 'Receipt'[Receipt] ),
FILTER (
ALLSELECTED ( 'Receipt'[Date].[Date] ),
[Date].[Date] <= MAX ( [Date].[Date] )
)
)
RETURN
totalstock + totalreceipt - totalConsumption``````

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 REPLIES 3
Community Support

Hi @Narayan ,

You can try this measure:

``````A =
VAR totalConsumption =
CALCULATE (
SUM ( 'Consumption'[Consumption] ),
FILTER (
ALLSELECTED ( 'Consumption'[Date].[Date] ),
[Date].[Date] <= MAX ( [Date].[Date] )
)
)
VAR totalstock =
SUM ( 'Stock'[Stock] )
VAR totalreceipt =
CALCULATE (
SUM ( 'Receipt'[Receipt] ),
FILTER (
ALLSELECTED ( 'Receipt'[Date].[Date] ),
[Date].[Date] <= MAX ( [Date].[Date] )
)
)
RETURN
totalstock + totalreceipt - totalConsumption``````

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Frequent Visitor
• Thanks for the reply, with this only per month quantities are evaluated, but it does not take into account the opening stock on next month onwards.
• The stock as on today is avaialble for current month only.
• While calculating the closing stock of current month we can have equation  = stock - consumption + receipt for current month
• For calculating the opening stock on next month the above becomes the opening stock of next month i.e closing stock of earlier month becomes opening stock on next month.
• So this we need to calculate month on month.
• How to calculate this ?
Super User

@Narayan , With help from date table, You have to follow a formula like

Inventory / OnHand
[Intial Inventory] + CALCULATE(SUM(Table[Ordered]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Sold]),filter(date,date[date] <=maxx(date,date[date])))

where [Intial Inventory]  is the first inventory. If this is the only inventory, then do the sum. else calculate based on min date

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Announcements

#### Launching new user group features

Learn how to create your own user groups today!