Subtracting values from previous row in a different column continuously (by retail week)

[Tommy_11]

Hi All, 

Let me explain a little bit the context of my problem, since I'm not allowed to share files.

• I have a table 'date' in which I have the dates and the retail week (we work by retail week, so dates are referring only to each Saturday of each week). I have added a calculated column in order to add the index of each week, as you can see below

• In another table ('Sales'), I have then the actual sales recorded and a measure that calculates the sales forecast by week;
• After that, I've created a couple of measures that gives me the current week "on hand quantity" of products in store, last week ''on hand quantity'' and this week sales (that provides the actual sales and for future weeks the forecasted quantities) as you can see below:

• I tried to use the formula below, but I can have the right result just for the first row (please see below the formula I've used and the screen):

Forecast EOH = 
VAR CurrentWeek =
    MAX('date'[Week Rank])
VAR LastWeek =
    CALCULATE(MAX('date'[Week Rank]) -1)
VAR LastActualWeek =
    CALCULATE (
        MAX ( 'date'[Week Rank] );
        'date'[Week Rank] = LastWeek;
        REMOVEFILTERS ()
    )
VAR ALLWeeks =
    SUMMARIZE ( ALL ( 'Date' ); 'date'[Week Rank] )
VAR T1 =
    FILTER (
        ALLWeeks;
        'date'[Week Rank] >= LastActualWeek
            && 'date'[Week Rank] <= CurrentWeek
    )
VAR T2 =
    ADDCOLUMNS (
        T1;
        "@Actual"; [Last Week EOH];
        "@Fct Sales"; [This Week Sales]
    )

RETURN
    IF (
        [This Week EOH] > 0;
        [This Week EOH] ;
        SUMX ( T2; [@Actual] - [@Fct Sales] )
    )

• I am not able to "mark as date table" my table 'date' (first print I've put above) because dates are not consecutive.

I am struggling with this part and I can't finish my project without it.

Can I please ask for your help? It would be super if you would be able to help me through this.

Thank you very much in advance

Tommy

[Tommy_11]

@lbendlin 

Current output:

Week	Week Rank	This Week Sales	This Week EOH	Last Week EOH	Forecast EOH
Wk 27	17	101	1623	1494	1623
Wk 28	18	63	1677	1623	1677
Wk 29	19	87	1778	1677	1778
Wk 30	20	85	1887	1778	1887
Wk 31	21	90	1990	1887	1990
Wk 32	22	110	2344	1990	2344
Wk 33	23	173	2441	2344	2441
Wk 34	24	126	2709	2441	2709
Wk 35	25	128		2709	2581
Wk 36	26	141			-141
Wk 37	27	131			-131
Wk 38	28	154			-154
Wk 39	29	132			-132
Wk 40	30	124			-124
Wk 41	31	71			-71
Wk 42	32	119			-119
Wk 43	33	141			-141
Wk 44	34	88			-88
Wk 45	35	97			-97
Wk 46	36	88			-88
Wk 47	37	92			-92
Wk 48	38	56			-56
Wk 49	39	164			-164
Wk 50	40	188			-188

Expected Output: 

Week	Week Rank	This Week Sales	This Week EOH	Last Week EOH	Forecast EOH
Wk 27	17	101	1623	1494	1623
Wk 28	18	63	1677	1623	1677
Wk 29	19	87	1778	1677	1778
Wk 30	20	85	1887	1778	1887
Wk 31	21	90	1990	1887	1990
Wk 32	22	110	2344	1990	2344
Wk 33	23	173	2441	2344	2441
Wk 34	24	126	2709	2441	2709
Wk 35	25	128		2709	2581
Wk 36	26	141			2440
Wk 37	27	131			2309
Wk 38	28	154			2155
Wk 39	29	132			2023
Wk 40	30	124			1899
Wk 41	31	71			1828
Wk 42	32	119			1709
Wk 43	33	141			1568
Wk 44	34	88			1480
Wk 45	35	97			1383
Wk 46	36	88			1295
Wk 47	37	92			1203
Wk 48	38	56			1147
Wk 49	39	164			983
Wk 50	40	188			795

Last Week EOH = CALCULATE(
	SUM(
		'Sales'[TTL EOH Ttl Qty]);
		'Sales'[Season] = "FW23";
		FILTER(ALL('date');'date'[Week Rank]=max('date'[Week Rank])-1))

This Week Sales = CALCULATE(
    [Fct Sls FW23 (2LW)]; 
    FILTER(ALL('date');
    'date'[Week Rank]=max('date'[Week Rank])))

Please find above data sample:

In green the right expected output I have for just the first row, while in blue the expected output I would like to acheive.

Thank you

[lbendlin]

something like this?

 

[Tommy_11]

Thank you for your help.

No, because from Week 36 on, I need to have the difference between Week 35 adjusted forecast and Week 36 This Week Sales.

In this case:

Week 35 (adjusted forecast) = 2709 (Last week EOH Week 35) - 128 (This Week Sales Week 35) --> 2581 (correct as you proposed).

Then, I have to subtract from 2581 the sales of the week after (Week 36) which are 141 --> Week 36 adjusted forecast 2440 (correct as you proposed).

Then, I have to subtract from 2440 the sales of the week after (Week 37) which are 131 --> Week 37 adjusted forecast should be = 2309 and so on.

[lbendlin · Accepted solution]

Sorry, had dropped the ALLSELECTED for some reason.

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...