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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Ramzan
Helper I
Helper I

dax for calculating opening and closing stock showing null result for inactive dates

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.

 

Untitled.jpg

 

 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".

Untitled2.jpg

 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".

Untitled3.jpg

 

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.

1 ACCEPTED 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

View solution in original post

5 REPLIES 5
Phil_Seamark
Employee
Employee

HI @Ramzan

 

Have you tried

 

Change =calculate(sum('Table'[Qty]), 'Table'[In/Out]="In")
-calculate(sum('Table'[Qty]), 'Table'[In/Out]="Out") + 0

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Yes. I tried just now.

But it is not working.Untitled11.jpg

 

 

 

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 .......


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!


@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".

 

Untitled2.jpg

 

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".

Untitled3.jpg

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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