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

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
ggyczew
Advocate I
Advocate 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):

ggyczew_0-1697614802904.png

 

After aggregation 

ggyczew_1-1697614935428.png

 

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)

ggyczew_2-1697616904266.png

 

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

ggyczew_3-1697617759513.png

 

 

 

 

1 REPLY 1
ggyczew
Advocate I
Advocate 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

ggyczew_0-1697801149138.png

 


 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.