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

jamesdanuk's avatar
jamesdanuk
Frequent Visitor
8 years ago

Hi Alex,

 

Apologies it should be 995, not 1005.

 

Does that help?

 

In regards to teh Visual i would in the report select a Reporting Date and the Report (Visual will display say bar chart with location or item ) 

 

Thanks

 

Dan

 

 

alexei7's avatar
alexei7
Continued Contributor
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