Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Dax Formula to calculate the 'closing inventory balance'

Answered Dax Formula to calculate the 'closing inventory balance'

  • Sunday, February 03, 2013 6:01 PM
     
     
    Sign In to Vote
    0

    Hi,

    I am looking for a formula that enables me to calculate the 'closing inventory balance'.

    I have datasets containing information about the 'sales amount' on day X. And datasets containing information about the purchases on day X or Y.

    How can I calculate 'inventory' = 'closing inventory balance' (last dataset before) - 'sales' + 'purchases' ?
    The last dataset can be 1day away from now or more days away from now. I have a column containing the 'booking date'.

    Which formula do I have to use and how do I have to organize the formula?

    Thank you very much for your answers.
    Regards
    Jörg

     
     

All Replies

  • Tuesday, February 05, 2013 2:37 PM
     
     
    Sign In to Vote
    0

    Is this what you are looking for ?

    http://www.sqljason.com/2012/06/lastnonempty-in-tabular-mode-part-1.html

    Microsoft Partner Services Organization

     
  • Tuesday, February 05, 2013 3:08 PM
     
     
    Sign In to Vote
    0

    Hi Rakesh,

    thank you for answering. I really dont know if this is what I am looking for.

    Can I calculate the inventory column from the attached picture with a formula from your link?

    Regards

    JörgCalculate remaining Inventory

    By the way: I work with a seperate Time Dimension.

    Moving Amount:

    =[Salesamount]+[Purchases]+[Production Amount]

    Inventory:

    =CALCULATE(SUM(FactSOP[Moving Amount]);DATESBETWEEN(DimTime[Date];BLANK();LASTDATE(DimTime[Date])))

    As it is a German version of PowerPivot I have to write ; instead of ,

    The formula for the Moving Amount works. The formula for the Inventory shows the same results as the formula for the Moving Amount. Is there a mistake? I need to receive the values of the picture for the inventory column.



    • Edited by J.Roca Tuesday, February 05, 2013 3:47 PM
    • Edited by J.Roca Tuesday, February 05, 2013 6:02 PM
    • Edited by J.Roca Tuesday, February 05, 2013 6:04 PM
    • Edited by J.Roca Tuesday, February 05, 2013 10:28 PM
    •  
     
  • Wednesday, February 06, 2013 12:00 AM
     
     Answered Has Code
    Sign In to Vote
    0

    Hi,

    Please find the below DAX Query.  I have written it as per your image and you may need to change it as per your requirement.

    Calculate (
  Sum ( Table1[ProductionAmount] ) + Sum ( Table1[Purchases] ) + Sum ( Table1[Salesamount] ),
  Filter (
    All ( Table1 ),
    Earlier ( Table1[Date] ) >= Table1[Date]
  )
)

    Microsoft Partner Services Organization

    • Marked As Answer by J.Roca Wednesday, February 06, 2013 12:36 PM
    •  
     
  • Wednesday, February 06, 2013 12:36 PM
     
     Answered Has Code
    Sign In to Vote
    0

    Hi Rakesh,

    thank you very much for your help! Your formula works quite well except the fact that it does not sum up the first dataset. I modified the formula a bit and found a solution:

    =CALCULATE (SUM[Moving Amount]);FILTER(ALLEXCEPT(Table1;Table1[Product]);EARLIER(Table1[Date]) >= Table1[Date]))

    Best Regards

    Jörg

    • Marked As Answer by J.Roca Wednesday, February 06, 2013 12:37 PM
    •