cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Frequent Visitor

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

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 =
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

1 ACCEPTED SOLUTION
Super User

Sorry, had dropped the ALLSELECTED for some reason.

5 REPLIES 5
Frequent Visitor

@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])))``````

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

Super User

something like this?

Frequent Visitor

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.

Super User

Sorry, had dropped the ALLSELECTED for some reason.

Super User

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.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors