topic Re: Subtracting Previous row from a value to get current row in DAX Commands and Tips

Subtracting Previous row from a value to get current row

KW123 — Fri, 22 Jul 2022 18:55:27 GMT

I need a DAX to calculate the following....

I have a Days column and an FD column. The FD column assigns each day a number, with the exception of Sunday (it doesn't get a number) and Friday and Saturday will always have the same number. If there is a holiday, it will get the same number as the previous date.

The DAX for FD is

FD = IF([FD2] = BLANK(),

BLANK(),

CALCULATE(MAX([FD2]),ALLEXCEPT('Dates','Dates'[YYYYMM2])) -[FD2]+1

FD2 = IF(WEEKDAY([Date],2)<>7,

RANKX(FILTER('Dates','Dates'[IsRank] && [YYYYMM2]=EARLIER([YYYYMM2])),[Date]),

BLANK()

)
The FD2 is the DESC of the FD.

I have a Values column which is the Monthly goal total. I need to work my way backwards from the example of $200 and minus the daily goal of $12 to get the row above it($188). For the next row, minus $12 from $188 etc... The tricky part is, I need a statement which will include that if the FD is the same, then the calculation needs to be the same value.

So if I have
Day FD Calculation
Tuesday 1 $152
Wednesday 2 $164
Thursday 3 $176
Friday 4 $188
Saturday 4 $188
Monday 5 $200

I hope that makes sense!

Re: Subtracting Previous row from a value to get current row

vapid128 — Fri, 22 Jul 2022 19:03:50 GMT

Find your max FD of the month

var _maxFD = Calculate(max(FD), ALLEXCEPT(table,table[month]))

return [Value] - (_maxFD-[FD])*[Daily Goal]

Re: Subtracting Previous row from a value to get current row

KW123 — Fri, 22 Jul 2022 19:12:12 GMT

vapid128
Thank you!!!!!
For the [value] I have it as a column and not a measure, is there a way to change that? Otherwise it won't let me add [values] into the dax

Re: Subtracting Previous row from a value to get current row

tamerj1 — Fri, 22 Jul 2022 19:48:23 GMT

Hi KW123

can you please provide copy paste sample data?

Re: Subtracting Previous row from a value to get current row

KW123 — Fri, 22 Jul 2022 19:50:46 GMT

tamerj1

Re: Subtracting Previous row from a value to get current row

tamerj1 — Fri, 22 Jul 2022 19:59:16 GMT

KW123

I need to copy and paste the values in order to generate sample file

Re: Subtracting Previous row from a value to get current row

KW123 — Fri, 22 Jul 2022 20:14:00 GMT

tamerj1

Assume monthly goal is $1000
Daily goal is $8
Calculation is monthly goal previous row-daily goal=current row while paying attention to the FD column. We will be working backwards from $1000

Day FD Calculation
1 Tuesday 1 $856
2 Wednesday 2 $864
3 Thursday 3 $872
4 Friday 4 $880
5 Saturday 4 $880

7 Monday 5 $888
8 Tuesday 6 $896
9 Wednesday 7 $904
10 Thursday 8 $912
11 Friday 9 $920
12 Saturday 9 $920

14 Monday 10 $928
15 Tuesday 11 $936
16 Wednesday 12 $944
17 Thursday 13 $952
18 Friday 14 $960
19 Saturday 14 $960

21 Monday (Holiday) 14 $960
22 Tuesday 15 $968
23 Wednesday 16 $976
24 Thursday 17 $984
25 Friday 18 $992
26 Saturday 18 $992

28 Monday 19 $1000

Re: Subtracting Previous row from a value to get current row

tamerj1 — Fri, 22 Jul 2022 21:18:19 GMT

HI KW123
Sorry for the late reply. I got distracted with some other business. Here is the a sample file with the solution

https://www.dropbox.com/t/63n8oSzVv2Ewm1Vt

Calculatetion = VAR MonthlyGoal = 1000 VAR DailyGoal = 8 VAR MaxFD = CALCULATE ( MAX ( Data[FD] ), ALLSELECTED ( Data ) ) VAR CurrentFD = MAX ( Data[FD] ) RETURN MonthlyGoal - ( MaxFD - CurrentFD ) * DailyGoal

Re: Subtracting Previous row from a value to get current row

vapid128 — Fri, 22 Jul 2022 21:30:16 GMT

I think your [value] [FD] and [Daily Goal] are all colnum.

Isn't it?

and Just add colnum

Re: Subtracting Previous row from a value to get current row

KW123 — Sat, 23 Jul 2022 02:16:17 GMT

tamerj1
This is great and looks like what I am looking for! The only issue is that the Monthly goal changes each month so I would like a DAX that I only have to plug into once. The current monthly goals I have are in a table where each month is different. Is there a way to do this?

Re: Subtracting Previous row from a value to get current row

KW123 — Sat, 23 Jul 2022 02:23:15 GMT

vapid128 Not sure what I am doing incorrectly.

Re: Subtracting Previous row from a value to get current row

vapid128 — Sat, 23 Jul 2022 02:48:21 GMT

You are missing a ")" at beginning of red line.
sum('Dates'[FD])

Re: Subtracting Previous row from a value to get current row

KW123 — Sat, 23 Jul 2022 02:52:52 GMT

vapid128

Thank you!!!! It's now saying too many arguments were passed to the SUM function but if I don't put the Sum it won't let me select the FD (it's a calculated column)

Re: Subtracting Previous row from a value to get current row

vapid128 — Sat, 23 Jul 2022 02:54:48 GMT

that should be MAX, not SUM.

Re: Subtracting Previous row from a value to get current row

tamerj1 — Sat, 23 Jul 2022 14:15:33 GMT

Hi KW123
Here is the updated sample file as requested https://www.dropbox.com/t/Ve5nnfpx1Ntmudau

FD Measure = SELECTEDVALUE ( Data[FD] )

Calculatetion = VAR MonthlyGoal = SELECTEDVALUE ( 'Monthly Target'[Target] ) VAR DailyGoal = 8 VAR CurrentFD = [FD Measure] VAR MaxFD = CALCULATE ( MAX ( Data[FD] ), REMOVEFILTERS ( ), VALUES ( 'Date'[Month] ) ) VAR Result = MonthlyGoal - ( MaxFD - CurrentFD ) * DailyGoal RETURN IF ( NOT ISBLANK ( CurrentFD ), Result )

Re: Subtracting Previous row from a value to get current row

KW123 — Mon, 25 Jul 2022 17:38:45 GMT

tamerj1
Thank you so much for taking the time to do that! I am not sure why it is not working for me. All rows are blank.

Re: Subtracting Previous row from a value to get current row

tamerj1 — Mon, 25 Jul 2022 17:42:55 GMT

KW123

Try to filter one month

Re: Subtracting Previous row from a value to get current row

KW123 — Mon, 25 Jul 2022 18:02:03 GMT

tamerj1 Doesn't seem to have an affect

Re: Subtracting Previous row from a value to get current row

tamerj1 — Mon, 25 Jul 2022 18:05:42 GMT

KW123

Would like to connect via zoom or teams to look into this problem?