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.
, 'Table'[In/Out]="In") - calculate(sum('Table'[Qty]
It is working fine.
2- Opening Stock = calculate ( [Movement], filter (all ('Table'[Date] ), 'Table'[Date]<max('Table'[
3- Closing Stock = calculate ( [Movement], filter (all ('Table'[Date] ), 'Table'[Date]<=max('Table'
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 grateful for any help.
The Pivot table I created is as given below.
My Sample excel file with Data Table Sample and Pivot Table is attached here.