cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

## Expiry Date - calculate Balance for each Stocks batch FEFO

Hi
I have problem to solve in DAX.

Input data are:

- historical Sales

- current Stock batches grouped by Expiry Date

Expiry Dates create Date Periods

Sales data is shifted by 12+ month and is used to determine quantity that will be sold in each Date Period.

All data is agregated by Date Period and evaluated in the following way:

1) If Sales<batch Stock -> the Ballance in negative and the company will have to dispose it

2) If Sales>batch Stock - > the Ballance is positive and full batch will be sold out and surplus will be sold(substarct) from next batch stocks.

Aim is to take in account surplus of Sales over Stock from previuos Date Periods.

Detailed Data might look like bellow (result of __DS0Core):

After aggregation

I need help to solve last step of calculation (at least the way I solved it in pure SQL without stored procedures)

For each Period I need preceding four Period Balances.
I have tried with OFFSET but i cannot meke it work in DAX

Final calculation is made as shown below
(For simplicity i give working SQL fromula instead description)

My problem with DAX is that its hard to implement this simple calculation given that I alread have summarized (grouped) data and only need to be able to get PeriodBalance from offset (previous) periods and interpret result row data.

For the test data results should be as below

After reading @Greg_Deckler article about strugle with CALCUHATE i came up with the solution that at least gives expected results.
The only problem is that I am extending manualy in DAX Studio visual's DAX code __DS0Core, and don't know if it is possible to return extended result of default PBI query in order to populate visual with my results.

Here is working DAX query

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.