Previous Week Inventory - this week Sales

Question

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.&nbsp;Thanks

jihwan_kim · Answer

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.
&nbsp;

&nbsp;
WINDOW function (DAX) - DAX | Microsoft Learn
&nbsp;
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
    )

&nbsp;

anonymous · Answer

Hi,&nbsp;Volvo_Chang&nbsp;
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]

&nbsp;
Here is my preview:

&nbsp;
How to Get Your Question Answered Quickly&nbsp;
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&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.

volvo_chang · Answer

Hello&nbsp;&nbsp;Jihwan_Kim,&nbsp;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!