Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

Continuous subtracting of values from previous row in a different column

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

Aruneesh123_1-1674577964261.png

 

 

 

  • 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
tamerj1
Super User
Super User

@Anonymous 
Following are my assumptions

  1. Year and YearQuarter are from a connected Date table.
  2. [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] )
    )

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

Tommy_11_0-1695651510708.png

 

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

Tommy_11_1-1695652003150.png

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

Tommy_11_2-1695652268686.png

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

Anonymous
Not applicable

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:

Aruneesh123_0-1675184127935.png

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:

Aruneesh123_1-1675184287099.png

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

 

@Anonymous 

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

 

Anonymous
Not applicable

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;

Aruneesh123_0-1675699830983.png

 

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

Aruneesh123_1-1675699830990.png

 

 

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:

Aruneesh123_2-1675699830996.png

 

 

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!

tamerj1
Super User
Super User

Hi @Anonymous 
Are Year and YearQuarter from a Date table?

Anonymous
Not applicable

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

@Anonymous 
Ok then please try my proposed solution in the other reply.

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Kudoed Authors