Opening and Closing Stock from a Stock Table log

[CHINH]

Hi All

I have a table of stock log which which will update latest stock by time daily, I want to:

1. Calculate opening Stock and closing stock of each month. the opening stock of this month will be the closing stock of previous month 

2. With months without Stock change date, the opening stock should be equal with previous months which have closing stock

Example as image below: 

Product_id : 46250

shop_id : 462

Latest Stock Qty of July= 1295 which should be display in August too.  

My relation like this

Thank you in advance for your support!

My data set as below.

Stock Total Log 

[speedramps]

Hi @CHINH 

Thanks for the thumbs up. 😀😀😀
I have answered your question andrpovided an example provong it works. It does what you askes.

Please be polite and quickly click the [accept as soution].

One question per ticket please. If you need to change or extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

If you quote @speedramps in your next tickets then I will then receive an automatic notification, and will be delighted to help you again.

[Ashish_Mathur]

Hi,

Is the time stamp really important?  If it is, then how does one interpret the same time stamp for a Product_ID and stock_ID combination?  Why is the time stamp the same in range A2:A3 of sheet1?

[CHINH]

Dear Ashir_Mathur

Yes timestamp is important, because Inventory changes in milliseconds. The date column have format as below

but I don't know how to display it in visualization report with full date as original

[Ashish_Mathur]

Hi,

I am sure someone else will be able to help you with this.

[speedramps]

 Click here to download the solution
Download pbix 
Please note I could only included the first few hundref records to save space.

How it works ...

Always create  a Calendar table for your reports

Create measure to get the closing stock

Closing stock = 
VAR endofperiod = MAX(Calandar[Date])
RETURN
CALCULATE(
    SUM(Iventory[QUANTITY]),
    ALL(Calandar),
    Calandar[Date] <= endofperiod
)

Build your report

Thanks for the clear description of the problem with example data. I wish everyone did that!

Remember we are unpaid volunteers, and you have got free expert help which took a lot of effort,
This solution works and does exactly what you asked.
So please be polite and quickly click the [accept as solution] and the thumbs up button to leave kudos. 

One question per ticket please. If you need to change or extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

If you quote @speedramps in your next tickets then I will then receive an automatic notification, and will be delighted to help you again.

Please now click the [accept as solution] and the thumbs up button.  Thank you.

[CHINH]

Dear Speedramps

Firstly,I appreciate your warm and generous assistance but the result is not as my requirement. From your sample report I want display like this: 

1. Opening stock of Sep should be 1294

2. Closing stock of Sep should be displayed = 0

3. Closing stock of Oct should be displayed = 0

4. Closing stock of Nov (this month) should be displayed = 0

[speedramps]

 Click here to download the solution which now inlcudes Opening and Closing stock.
Please be polite and click thumbs up for at least helping, 
and then also click Accept Solution if it fixes your problem.
Download PBIX 
Please note I could only included the first few hundref records to save space.

How it works ...

Always create  a Calendar table for your reports

Create measures to get the opening and closing stock

Opening stock = 
VAR startofperiod = MIN(Calandar[Date])
RETURN
CALCULATE(
    SUM(Iventory[QUANTITY]),
    ALL(Calandar),
    Calandar[Date] < startofperiod
)

Closing stock = 
VAR endofperiod = MAX(Calandar[Date])
RETURN
CALCULATE(
    SUM(Iventory[QUANTITY]),
    ALL(Calandar),
    Calandar[Date] <= endofperiod
)

Build your report

Thanks for the clear description of the problem with example data. I wish everyone did that!

Remember we are unpaid volunteers, and you have got free expert help which took a lot of effort,
This solution works and does exactly what you asked.
So please be polite and quickly click the [accept as solution] and the thumbs up button to leave kudos. 

One question per ticket please. If you need to change or extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

If you quote @speedramps in your next tickets then I will then receive an automatic notification, and will be delighted to help you again.

Please now click the [accept as solution] and the thumbs up button.  Thank you.

[CHINH]

Dear speedramps

Thank you for your help, but your sample result is not my desire, each row in my dataset is latest stock of each timestamp, . If summarizing Quantity column which will sum latest stock of each Day and timestamp. (Each row of dataset is not Stock in Transaction so I cannot summarization)

I want to display as below