Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hy all
Greetings and thanks in advance.
I have a large data table in Data Model in excel 2016. This table is in SQL Server and is connected to Data Model in excel with Power Query. It has millions of rows. I have created pivot table report on it.
I need to calculate opening and closing stock for selected period (date, month or year). The sample of data is as given below. I am showing just a few columns and records in sample.
I have added 3 measures in the Pivot table fields.
1- Change =calculate(sum('Table'[Qty]), 'Table'[In/Out]="In")
-calculate(sum('Table'[Qty]), 'Table'[In/Out]="Out")
2- Opening Stock =CALCULATE ( [change],
FILTER (
ALLEXCEPT ( 'Table', 'Table'[Item Name]),
'Table'[Date] < MAX ( 'Table'[Date])))
3- Closing Stock =CALCULATE ( [change],
FILTER (
ALLEXCEPT ( 'Table', 'Table'[Item Name]),
'Table'[Date] <= MAX ( 'Table'[Date])))
Problem : In filter If I select a date for which there is null data in the "Change" then opening Stock and Closing Stock is also shown Null even if opening stock and closing stock for that date is not null.
The Pivot table I created is as given below.
The pivot table 1 is showing complete data for the date "30-04-2018".
But the Pivot table 2 is not showing any data in any field and it is showing a blank row.
It should show 2-lines. (1) "Bread" with 30 Value in "Opening Stock" & "Closing Stock" and (2) "Rusk" with value 26 in "Opening Stock" & "Closing Stock".
Please suggest me some change in dax formula or some new formula for solution or a complete new scenario for it.
I will be very grateful for help.
Solved! Go to Solution.
I have got the solution by doing with completely different scenario that i have shared on another post.
Detail can be seen from that post.
Here is the link of that post.
Problem in DAX for Calculating Opening and Closing Stock for Inventory Report
HI @Ramzan
Have you tried
Change =calculate(sum('Table'[Qty]), 'Table'[In/Out]="In") -calculate(sum('Table'[Qty]), 'Table'[In/Out]="Out") + 0
Yes. I tried just now.
But it is not working.
Hi @Ramzan
Can you please point out what is the wrong value according to your sample data.
eg. that the value in the "Change" column is showing (12.00) for the row 15-08-2017 and it should be .......
@Phil_Seamark wrote:Hi @Ramzan
Can you please point out what is the wrong value according to your sample data.
eg. that the value in the "Change" column is showing (12.00) for the row 15-08-2017 and it should be .......
I have edited the original post.
So now reply to your comment is as given below.
Problem : In filter If I select a date for which there is null data in the "Change" then opening Stock and Closing Stock is also shown Null even if opening stock and closing stock for that date is not null.
The Pivot table I created is as given below.
The pivot table 1 is showing complete data for the date "30-04-2018".
But the Pivot table 2 is not showing any data in any field and it is showing a blank row.
It should show 2-lines. (1) "Bread" with 30 Value in "Opening Stock" & "Closing Stock" and (2) "Rusk" with vaklue 26 in "Opening Stock" & "Closing Stock".
I have got the solution by doing with completely different scenario that i have shared on another post.
Detail can be seen from that post.
Here is the link of that post.
Problem in DAX for Calculating Opening and Closing Stock for Inventory Report
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |