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

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

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
Helper I
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

ggyczew_0-1697801149138.png

 


 

Helpful resources

Announcements
Fabric Community Conference

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.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

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 Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors