topic Re: Previous Week Inventory - this week Sales in DAX Commands and Tips

Previous Week Inventory - this week Sales

Volvo_Chang — Mon, 29 Apr 2024 22:31:02 GMT

Hello,

I like to create a new dax column based on previous week inventory - this week sales and be able to predict for the future.

First, there is no inventory data value past the current week so it is blank for the inventory past the current week.

So the dax is trying to grab the previous week inventory - this week sales as the new inventory for this week invenotry and this week inventory - next week sales(average baseline) as next week inventory prediction.

My problem is whenever there is no inventory past the current week, the new dax column is not sum up week over week calulation but only negative sales since that is the only cells has value past the current week.

Thanks

Re: Previous Week Inventory - this week Sales

Jihwan_Kim — Tue, 30 Apr 2024 03:15:28 GMT

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

WINDOW function (DAX) - DAX | Microsoft Learn

expected result measure: = VAR _startdate = CALCULATE ( MIN ( data[week_end_date] ), REMOVEFILTERS () ) VAR _startinginventory = CALCULATE ( SUM ( data[inventory] ), data[week_end_date] = _startdate ) VAR _previousinventorycumulate = CALCULATE ( SUM ( data[inventory] ), WINDOW ( 1, ABS, -1, REL, ALL ( data[week_end_date] ), ORDERBY ( data[week_end_date], ASC ) ) ) VAR _salescumulate = CALCULATE ( SUM ( data[sales] ), WINDOW ( 2, ABS, 0, REL, ALL ( data[week_end_date] ), ORDERBY ( data[week_end_date], ASC ) ) ) RETURN SWITCH ( TRUE (), MAX ( data[week_end_date] ) = _startdate, _startinginventory, _previousinventorycumulate - _salescumulate )

Re: Previous Week Inventory - this week Sales

Anonymous — Tue, 30 Apr 2024 03:42:25 GMT

Hi, Volvo_Chang

Since I don't know your data, I've created a sample table:

Then create new columns:

CurrentWeekInventory = CALCULATE( SUM('Table'[Inventory]), FILTER( 'Table', 'Table'[Week] = EARLIER('Table'[Week]) - 1 ) ) - 'Table'[Sales]

NextWeekInventoryPrediction = 'Table'[CurrentWeekInventory] - 'Table'[NextWeekSalesBaseline]

Here is my preview:

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data)

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Previous Week Inventory - this week Sales

Volvo_Chang — Tue, 30 Apr 2024 21:37:46 GMT

Hello Jihwan_Kim,

Thanks. I think we are getting close. Instead of the first date you have inventory, I want the start inventory is based on the week of today. I have week indexing for my date table so I like to pin the start inventory is max(week index) = week index(today()). And I don't have next week inventory that will be this week invnetory - this week sales( I know I say next week but this week will work as well).

Thanks!

Re: Previous Week Inventory - this week Sales

Volvo_Chang — Tue, 30 Apr 2024 21:56:04 GMT

Below is a screenshot of my dataset. supplier inventory QOH row 53 = supplier inventory QOH row 52 + forecasted prodcution row 53 - baseline row 53.

supplier inventory QOH row 54 = supplier inventory QOH row 53 + forecasted prodcution row 54- baseline row 54.

Re: Previous Week Inventory - this week Sales

Volvo_Chang — Mon, 13 May 2024 20:43:00 GMT

Hello Jihwan_Kim ,

Just like to follow up on this one. Thanks!

Re: Previous Week Inventory - this week Sales

Jihwan_Kim — Tue, 14 May 2024 03:31:51 GMT

Hi,

Thank you for your message, and please share your sample pbix file's link, and then I can try to look into it.

Re: Previous Week Inventory - this week Sales

Volvo_Chang — Tue, 14 May 2024 15:08:50 GMT

Hello Jihwan,

Thank you so much for reply.

Please see below with my dummy dataset and screenshot.

Basically, I like to create a measure or dax to be able to take the most recent supplier invenotry QOH from row 11 + row 12 forecast production - row 12 baseline = row 12 supplier inventory. Row 13 supplier inventory qoh = row 12 supplier invneoty 923,607(from calculation above)+ 72000-17933. and the sequence continues to the alst row.https://drive.google.com/file/d/1nSIRnbtyE_AwmjD9C2EBWanVzyuUD4Ot/view?usp=sharing