Forum Discussion

jamesdanuk's avatar
jamesdanuk
Frequent Visitor
8 years ago
Solved

Stock On Hand

Hi,   I am using Power BI (Direct Query) from a SQL Source, and am having problems doing a Closing Stock Balance.   I have in a SQL Table the following structure   Date Entry Type Quantity...
  • alexei7's avatar
    alexei7
    8 years ago

    Hi Dan,

     

    I think something like the following might help you achieve what you want (although difficult to be certain without knowing your model):

     

    Closing Stock Balance =
    CALCULATE (
     SUM ( Table1[Quantity] ),
     FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
    )

     

    Obviously, you'll need to change any table or column names to your own.

     

    Hope that helps,

    Thanks

    Alex

Anonymous's avatar
Anonymous
Not applicable
8 years ago

HI jamesdanuk,

 

You can add calculate column to replace entry type to 'stock in' and 'stock out', then remove 'data', 'entry type' and add above column to show summary amount.

 

Regards,

Xiaoxin Sheng

  • jamesdanuk's avatar
    jamesdanuk
    Frequent Visitor
    8 years ago

    Hi Xiaoxin,

    I am unsure what you mean? are you able to demostrate how to do this ?

     

    Thanks

     

    Dan