Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
CHINH
Frequent Visitor

Opening and Closing Stock from a Stock Table log

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.  

CHINH_1-1699203339954.png

 

My relation like this

CHINH_2-1699203760147.png

 

Thank you in advance for your support!

 

My data set as below.

Stock Total Log 

8 REPLIES 8
speedramps
Super User
Super User

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
Super User
Super User

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?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Dear Ashir_Mathur

 

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

CHINH_0-1699247123396.png

 

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

CHINH_1-1699247365404.png

 

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
speedramps
Super User
Super User

 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

speedramps_0-1699206695750.png

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

speedramps_1-1699206821716.png

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.

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

 

CHINH_0-1699239320295.png

 

 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

speedramps_0-1699206695750.png

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

speedramps_0-1699260823826.png

 

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.

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

CHINH_0-1699323913345.png

 

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors