Answered by:
Dax Formula to calculate the 'closing inventory balance'
Question

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
Sunday, February 3, 2013 6:01 PM
Answers

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] ) )
 Marked as answer by J.Roca Wednesday, February 6, 2013 12:36 PM
Wednesday, February 6, 2013 12:00 AM 
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 6, 2013 12:37 PM
Wednesday, February 6, 2013 12:36 PM
All replies

Is this what you are looking for ?
http://www.sqljason.com/2012/06/lastnonemptyintabularmodepart1.html
Tuesday, February 5, 2013 2:37 PM 
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örg
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 5, 2013 10:28 PM
Tuesday, February 5, 2013 3:08 PM 
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] ) )
 Marked as answer by J.Roca Wednesday, February 6, 2013 12:36 PM
Wednesday, February 6, 2013 12:00 AM 
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 6, 2013 12:37 PM
Wednesday, February 6, 2013 12:36 PM