Help for Calculation

[Narayan]

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.

[v-yingjl · Accepted solution]

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.

[Narayan]

• 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 ?

[amitchandak]

@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