How to calculate opening /closing stock using DAX

[Antros]

Hi all, 

I have the following data set and I am trying to calculate the opening and closing stock in each month. 

Date	Product name	OPENING STOCK	Purchases	Sales	CLOSING STOCK
01-Jan-21	Product 1	1400	100	-300	1200
01-Jan-21	Product 2	200	200	-300	100
01-Jan-21	Product 3	400	300	-200	500
01-Jan-21	Product 4	1000	400	-100	1300
01-Feb-21	Product 1	1200	100	-300	1000
01-Feb-21	Product 2	100	50	-100	50
01-Feb-21	Product 3	500	300	-200	600
01-Feb-21	Product 4	1300	400	-100	1600
01-Mar-21	Product 1	1000	100	-300	800
01-Mar-21	Product 2	50	450	-300	200
01-Mar-21	Product 3	600	300	-200	700
01-Mar-21	Product 4	1600	400	-100	1900
01-Apr-21	Product 1	800	100	-300	600
01-Apr-21	Product 2	200	200	-150	250
01-Apr-21	Product 3	700	300	-200	800
01-Apr-21	Product 4	1900	400	-100	2200

When I select the exact month then the correct opening and closing stocks are shown within the dashboard  

Whereas if I select more than one month then the opening and closing stock sums all the previous months and does not show the correct closing balance  

Thank you in advance for your support!

[Antros]

Thank you for the response!

Unfortunately this does not resolve the issue when you are trying to see the by product analysis.

As shown in the image below the closing stock using the measure  

Closing stock = CALCULATE(SUM('Sample data'[CLOSING STOCK]),FILTER(ALLSELECTED('Sample data'),'Sample data'[Date] <= MAX('Sample data'[Date]))) shows the same closing stock for all the producs which is not the case

[Antros · Accepted solution]

The below helped me resolve the issue: 

The opening and closing cannot be aggregated over the Date dimension.

I have created measures to handle it correctly as below:

Opening = CALCULATE(SUM(CLOSING_STOCK), FIRSTDATE(DATE))

Closing  = CALCULATE(SUM(CLOSING_STOCK), LASTDATE(DATE))

[ddpl]

@Antros But mai I know why are you using measure in table for closing stock instead of closing stock column.

[Antros]

Hi apologies for the confusion, the reason that I am trying to put a measure is because the actual closing stock is included in my data set and I don't want to do any sum. 

In the example provided the actual closing stock for Αpr-21 is €3.850 and not €13.800

The problem that I am facing is when I select more than one month in the slicer or even if I don't select any month then in the table shows the aggregate amount of €13.800 instead of €3.850

[ddpl]

@Antros try this

Create Measure as per below:

Measure = CALCULATE(SUM('Table'[CLOSING STOCK]),FILTER(ALLSELECTED('Table'),'Table'[Date] <= MAX('Table'[Date])))

OR YOU CAN USE WATERFALL CHART AS PER BELOW:

Here did not required to create any measure