- Power BI forums
- Updates
- News & Announcements
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish forums
- Translated Spanish Desktop
- Power Platform Integration - Better Together!
- Power Platform Integrations (Read-only)
- Power Platform and Dynamics 365 Integrations (Read-only)
- Training and Consulting
- Instructor Led Training
- Dashboard in a Day for Women, by Women
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Blog
- Power BI Community Blog
- Custom Visuals Community Blog
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

- Power BI forums
- Forums
- Get Help with Power BI
- DAX Commands and Tips
- Re: Continuous subtracting of values from previous...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Continuous subtracting of values from previous row in a different column

01-24-2023
08:39 AM

Hi all,

I am working on a Power BI dashboard that is forecasting values up until 2030, depending on projects that are under implementation between 2022 to 2030.

- I have one table [Actual] that contains actual values from 2021 and 2022 which I have at a monthly frequency.
- I also have another table [Project Impacts] that contains the projects, with the impacts that they have on the actual values, and this is available from 2022 to 2030, at a quarterly frequency.
- There are a few important measures relevant to this problem.
- From the [Actual Values] table, we have ‘Actual Values’.
- From the [Project Impacts] table, we have a measure named ‘Estimated saving from projects’, which calculates how much savings there are based on the projects each quarter. The table below shows the measures next to each other.

- What I want to do is subtract the ‘Estimated savings from projects’ from the ‘Actual Values’, so that I am left with the ‘Forecast Values’. This is the results I get after doing this (image below).

- As you can see from the image above, the ‘Forecast Values’ seems to be working properly, up until 2023/Q1. This is because there is no current data for 'Actual Values' after 2022, so the ‘Estimated saving from Projects’ is just subtracting from 0, hence the negative values in the far right column.
- What I want to do is keep the calculation the same up until the end of 2022, but after 2022, the ‘Estimated saving from Projects’ should be subtracted from the last quarters value.
- So as an example, the 2023/Q1 ‘Estimated saving from Projects’ value of 7.87 should be subtracted from the 53.51 value from the 2022/Q4 value in the ‘Forecast Value’ measure, giving a value of 45.64 instead of the value -7.57. Then the same process should repeat, so the 7.87 from 2023/Q2 should be subtracted from the 45.64 from the previous quarter and so on.

Does anyone know of a DAX formula, calculated column, or any solution to the problem above?

Many thanks

8 REPLIES 8

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

01-24-2023
09:41 AM

@Aruneesh123

Following are my assumptions

- Year and YearQuarter are from a connected Date table.
- [Actual Values] and [Estimated Saving From Projects] are measures.

Then you may try the following measure

```
Forecast Values =
VAR CurrentQuarter =
MAX ( 'Date'[YearQuarter] )
VAR LastActualDate =
CALCULATE ( MAX ( Actual[Date] ), REMOVEFILTERS () )
VAR LastActualQuarter =
CALCULATE (
MAX ( 'Date'[YearQuarter] ),
'Date'[Date] = LastActualDate,
REMOVEFILTERS ()
)
VAR ALLYearsAndQuarters =
SUMMARIZE ( ALL ( 'Date' ), 'Date'[Year], 'Date'[YearQuarter] )
VAR T1 =
FILTER (
ALLYearsAndQuarters,
'Date'[YearQuarter] >= LastActualQuarter
&& 'Date'[YearQuarter] <= CurrentQuarter
)
VAR T2 =
ADDCOLUMNS (
T1,
"@Actual", [Actual Values],
"@Saving", [Estimated Saving From Projects]
)
RETURN
IF (
CurrentQuarter < LastActualQuarter,
[Actual Values] - [Estimated Saving From Projects],
SUMX ( T2, [@Actual] - [@Saving] )
)
```

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

09-25-2023
07:40 AM

Hi @tamerj1 ,

since it seems that your advices hepled user, I have a similar problem.

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

- I have another table 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

- I have then the actual sales recorded and a measure that calculates the 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 you provided above, but I can have the right result just for the first row (please see below the screen and the formula I've used):

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

01-31-2023
09:00 AM

Hi @tamerj1

Thank you very much for the prompt response! Sorry i took so long to reply as well, havent been able to get back to this piece of work since last week.

I tried your forumla and it seems to work if i alter the above fromula for just Year instead of YearQuarter as seen below:

This is what i want which is perfect.

However when using the YearQuarter fromula you have given, it doesnt seem to be working in the same way as shown below:

Is there a way so that I am able to end up with the same value at the end as the Years Table?

Please let me know if you need anything else clarifying.

Many thanks

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

01-31-2023
10:30 AM

It is not easy to write such a code without working on a sample file. I'm trying to imagine how should be the DAX that can produce such result. However, Please try the following (sorry for the bad format as the code format does not work on the phone)

Forecast Values Quartets =

VAR CurrentQuarter =

MAX ( 'Date'[YearQuarter] )

VAR LastActualDate =

CALCULATE ( MAX ( Actual[Date] ), REMOVEFILTERS () )

VAR LastActualQuarter =

CALCULATE (

MAX ( 'Date'[YearQuarter] ),

'Date'[Date] = LastActualDate,

REMOVEFILTERS ()

)

VAR ALLYearsAndQuarters =

SUMMARIZE ( ALL ( 'Date' ), 'Date'[Year], 'Date'[YearQuarter] )

VAR T1 =

FILTER (

ALLYearsAndQuarters,

'Date'[YearQuarter] >= LastActualQuarter

&& 'Date'[YearQuarter] <= CurrentQuarter

)

VAR T2 =

ADDCOLUMNS (

T1,

"@Actual", CALCULATE ( [Actual Values], ALL ( 'Date'[YearQuarter] ) ),

"@Saving", [Estimated Saving From Projects]

)

RETURN

IF (

CurrentQuarter < LastActualQuarter,

[Actual Values] - [Estimated Saving From Projects],

SUMX ( T2, DIVIDE ( [@Actual] - [@Saving], 4 ) )

)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

02-06-2023
08:10 AM

Hi @tamerj1

Unfortunately I am unable to share the sample file, as it contains protected information, but I can try to explain the situation better.

I tried the most recent code that you gave, and it doesn’t seem to subtract the correct values. It looks like the formula that you gave divides the difference between the actual values and the estimated savings from projects by 4, but the data is already split by quarters already this won’t show the correct values.

However I think I know what is the solution that I’m trying to work towards. As I mentioned before, the years formula works just fine, as seen in the table below;

The Quartets formula that you gave doesn’t seem to subtract the correct values, as seen below

If we go back to the original formula that you gave for the quarters calculation, the subtraction was correct, but because the data was split by quarters, and the number that was used to in the subtraction was wrong. Below is the table for the original quarters table:

In the above table, the subtraction starts happening from 2022/Q1 where the value is 52.48, but in the years table, in the Years table, the subtraction happens from 2022 where the value is 176.1. Is there a way of making it so that within the quarters formula, we start subtracting from the total years sum? So in the above table, we start of with the summation of the previous year value, and start subtracting from that. This would stop the problem of the values becoming negative.

Also as a side note, is there any way of somehow excluding the ‘estimated savings from projects’ in 2022 as we already have the 2022 full year values? Is it possible to include this within the formulas calculating the forecasting values?

Please let me know if you understand and need any other clarification.

Again many thanks for your help!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

01-25-2023
02:08 AM

Sorry i forgot to mention this, yes year and year quarter are from a date table

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content