Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
Tommy_11
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

Tommy_11_0-1695651510708.png

 

  • 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:

Tommy_11_1-1695652003150.png

 

  • 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] )
    )

Tommy_11_2-1695652268686.png

 

  • 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

Sorry, had dropped the ALLSELECTED for some reason.

 

lbendlin_0-1696261129833.png

 

View solution in original post

5 REPLIES 5
Tommy_11
Frequent Visitor

@lbendlin 

Current output:

WeekWeek RankThis Week SalesThis Week EOHLast Week EOHForecast EOH
Wk 2717101162314941623
Wk 281863167716231677
Wk 291987177816771778
Wk 302085188717781887
Wk 312190199018871990
Wk 3222110234419902344
Wk 3323173244123442441
Wk 3424126270924412709
Wk 3525128 27092581
Wk 3626141  -141
Wk 3727131  -131
Wk 3828154  -154
Wk 3929132  -132
Wk 4030124  -124
Wk 413171  -71
Wk 4232119  -119
Wk 4333141  -141
Wk 443488  -88
Wk 453597  -97
Wk 463688  -88
Wk 473792  -92
Wk 483856  -56
Wk 4939164  -164
Wk 5040188  -188

 

Expected Output: 

WeekWeek RankThis Week SalesThis Week EOHLast Week EOHForecast EOH
Wk 2717101162314941623
Wk 281863167716231677
Wk 291987177816771778
Wk 302085188717781887
Wk 312190199018871990
Wk 3222110234419902344
Wk 3323173244123442441
Wk 3424126270924412709
Wk 3525128 27092581
Wk 3626141  2440
Wk 3727131  2309
Wk 3828154  2155
Wk 3929132  2023
Wk 4030124  1899
Wk 413171  1828
Wk 4232119  1709
Wk 4333141  1568
Wk 443488  1480
Wk 453597  1383
Wk 463688  1295
Wk 473792  1203
Wk 483856  1147
Wk 4939164  983
Wk 5040188  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

something like this?

 lbendlin_2-1696257382657.png

 

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.

Sorry, had dropped the ALLSELECTED for some reason.

 

lbendlin_0-1696261129833.png

 

lbendlin
Super User
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.

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors