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

alexei7's avatar
alexei7
Continued Contributor
8 years ago

So sales are not included in your calculation?

 

Also, what result would you like to see? I'm imagining a visual metric for total sales whereby you filter on one date to see "Closing Stock" for that data, but would be helpful if you can confirm

 

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