cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Narayan
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.

 

Narayan_0-1632129672536.png

 

1 ACCEPTED SOLUTION
v-yingjl
Community Support
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

vyingjl_0-1632377908228.png

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.

View solution in original post

3 REPLIES 3
v-yingjl
Community Support
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

vyingjl_0-1632377908228.png

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.

View solution in original post

Narayan
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 ?
amitchandak
Super User
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!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.