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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Helper I

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

1 REPLY 1
Helper I

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

## Helpful resources

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

#### Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors
Top Kudoed Authors