Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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!
Solved! Go to 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))
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
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))
@Antros But mai I know why are you using measure in table for closing stock instead of closing stock column.
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
@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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
93 | |
87 | |
80 | |
69 | |
68 |
User | Count |
---|---|
226 | |
129 | |
119 | |
83 | |
77 |