Subtracting Previous row from a value to get current row

[KW123]

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! 

[tamerj1]

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 )

[KW123]

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

[tamerj1]

@KW123 

Try to filter one month

[KW123]

@tamerj1 Doesn't seem to have an affect 

[tamerj1]

@KW123 

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

[tamerj1]

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

[KW123]

@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?

[tamerj1]

Hi @KW123 

can you please provide copy paste sample data?

[KW123]

@tamerj1 

[tamerj1]

@KW123 

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

[KW123]

@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

[vapid128]

Find your max FD of the month

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

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

[KW123]

@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

[vapid128]

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

Isn't it?

and Just add colnum

[KW123]

@vapid128 Not sure what I am doing incorrectly. 

[vapid128]

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

[KW123]

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

[vapid128]

that should be MAX, not SUM.