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 to Calculate Opening and Closing Stock including Previous Period's Records

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

 

Untitled.png

 

I have added 3 measures in the Pivot table fields.

1-Movement=calculate(sum('Table'[Qty]), 'Table'[In/Out]="In")

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

It is working fine.

2-Opening Stock=calculate ( [Movement],

filter

 (all ('Table'[Date] ),

'Table'[Date]<max('Table'[Date] )))

3-Closing Stock=calculate ( [Movement],

filter

 (all ('Table'[Date] ),

'Table'[Date]<=max('Table'[Date] )))

There is a problem in the results of measure No. 2 and 3.

The result is ok if pivot table is viewed for all periods without filtering. But when I filter period (Date, Month or Year)  in pivot table, then result is not correct. It does not bring forward the closing stock of previous period.

Please suggest me some change in dax formula or some new formula for opening and closing stock calculation including previous period's data.

I will be very gratefull for any help.
The Pivot table I created is as given below.
Untitled2.pngUntitled3.png



1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

Hi

I modify two measures above with these:

 

Opening Stock =
CALCULATE (
    [Movement],
    FILTER (
        ALLEXCEPT ( Table1, Table1[Iteam Name] ),
        'Table1'[Date] < MAX ( 'Table1'[Date] )
    )
)

 

 

Closing Stock =
CALCULATE (
    [Movement],
    FILTER (
        ALLEXCEPT ( Table1, Table1[Iteam Name] ),
        'Table1'[Date] <= MAX ( 'Table1'[Date] )
    )
)

Finally, I get this result.

 

4.png

5.png

 

Best Regards

Maggie

View solution in original post

2 REPLIES 2
v-juanli-msft
Community Support
Community Support

Hi

I modify two measures above with these:

 

Opening Stock =
CALCULATE (
    [Movement],
    FILTER (
        ALLEXCEPT ( Table1, Table1[Iteam Name] ),
        'Table1'[Date] < MAX ( 'Table1'[Date] )
    )
)

 

 

Closing Stock =
CALCULATE (
    [Movement],
    FILTER (
        ALLEXCEPT ( Table1, Table1[Iteam Name] ),
        'Table1'[Date] <= MAX ( 'Table1'[Date] )
    )
)

Finally, I get this result.

 

4.png

5.png

 

Best Regards

Maggie

Is it possible to incorporate a max inventory allowed? For example, the max inventory is 25 and minimum inventory is 5. The opening inventory on 8/31 and 10/15 would have the 5 (minimum allowed) and the inventory on 6/1 would be 25 instead of 30, since 25 is the max. I would then add a column that would reflect the overage (such as "-2" for the days when there were only 3 and "5" for the day inventory exceeded 25).

 

52198A13-6198-4AD8-A0E7-BF45FA3757CC.png

 

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.