Previous Week Inventory - this week Sales

[Volvo_Chang]

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

[v-yohua-msft]

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.

[Jihwan_Kim]

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
    )

[Volvo_Chang]

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!

[Volvo_Chang]

Hello @Jihwan_Kim ,

Just like to follow up on this one. Thanks!

[Jihwan_Kim]

Hi,

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

[Volvo_Chang]

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 

[Volvo_Chang]

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.