how to show stock on hand balance for each Month for the whole year

[Khathu_King]

Good day 

Please help me . I am trying to build report that will show the stock on hand balances for each month and years, I am able to build report that show the current Stock on hand based on the date selected, but to show the stock on hand balance for each month that is where I am struggling. my stock transactions table include both sales,returns  and purchases,returns,

please see attached Power BI snip,on the snip the total after december month show the correct value but all othe balances before are not correct and also have negative value which i dont understand why as i dont have negatove stock on my database . 

[v-xiaotang · Accepted solution]

Hi @Khathu_King 

Could you provide

(1) a sample file, you can replace raw data with bogus data to protect your privacy,

(2) or provide some sample data that fully covers your question. 

(3) the dax expression of measures or calculated columns in the picture.

Thanks.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[v-xiaotang · Accepted solution]

Hi @Khathu_King 

Could you provide

(1) a sample file, you can replace raw data with bogus data to protect your privacy,

(2) or provide some sample data that fully covers your question. 

(3) the dax expression of measures or calculated columns in the picture.

Thanks.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[Khathu_King]

 

@v-xiaotang 

 Please find attched picture of excel data. I am trying to get stock on hand on each and every month from 2019 to 2020, GRV represent Stock IN and INV Representing Stock Out .

please let me know if you need more information 

 

[v-xiaotang]

Hi @Khathu_King 

what do you put in the visual (negative value)? A measure? Could you share it?

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[Khathu_King]

Hi @v-xiaotang 

Yes it is the Measure build as follows

1)Stock in =IF(('Table1'[Debit]>0,'Table1'[Quantity])

2)Stock Out=IF('Table1'[Credit]>0,'table1'[Quantity])

3)Stock on hand =Stock IN -Stock Out

4)And column I used Month column from my Calender

[v-xiaotang · Accepted solution]

Hi @Khathu_King 

The dax code you shared are columns, I put it into my sample file and find these columns fail to get the previous valus, you need to use function such like sumx().

Test1 = // measure
    var _EachStock_ID=SUMX(FILTER(ALLSELECTED(Table1),Table1[Stock_ID]=MIN(Table1[Stock_ID]) && Table1[Date] <= MIN(Table1[Date])),[Stock in]-[Stock Out])
return _EachStock_ID

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[Khathu_King]

Thank you @v-xiaotang  

this has resolved my problem.

the only issue is that my server cannot handle the Measure it is showing out of memory error after applying teh measure to my visual 

[amitchandak]

@Khathu_King , if the balance is on the last date of the month, you can consider closingbalancemonth

refer

https://www.youtube.com/watch?v=yPQ9UV37LOU

Also having how to use lastnonblankvalues

[Khathu_King]

@amitchandak the formulas on the video does not work on this instance because the stock on hand at month end is balance to be carried forward to the next Month,the formulas on the video caters for e.g Finding stock sold per month or sales per month or quarter or year,

i dont know if i am clear enough for you, check below an example 

1) 1-January 2019 purchased 100 quantities  of item A

2) 15 January 2019  Sold 10 Quantities 

---At Month we have 90 quanties on hand --This should show as stock on hand for January 

3) in Feb 2019 Puchased 30 Quantities

At month end of Feb we ahve 120 Quantities on Hand --This should show as stock on hand 

4)March We sold 50 Quanties 

at month end we have 70 Quantities ---this should show as stock on hand for march.

5)if there are no  more transaction for the year 2019 -the year 2019 will close with 70 stock on hand 

6) So january of 2020 should start with 70 on hand 

 6.1)if we sold 20 in january 2020

closing stock should be 50-and 50 Should show as stock on hand for January 2020

-----etc that is teh logic behind it i hope that nmy example will make you understand