Forum Discussion
Subtracting two rows
- 4 years ago
Hi KW123
Sorry it was too late yesterday I couldn't reply to you. Here is a sample file with the solution jnowing that you will retun back to me with more information that you've been hiding as usual 😉https://www.dropbox.com/t/lzKxh0DxLA056sy7Result = VAR A = 1000 VAR B = 10 VAR CurrentIndex = SELECTEDVALUE ( Data[INDEX] ) VAR MaxIndex = CALCULATE ( MAX ( Data[INDEX] ), ALLEXCEPT ( Data, Data[Day].[Month] ) ) RETURN IF ( CurrentIndex <> BLANK ( ), A - ( MaxIndex - CurrentIndex ) * B ) - 4 years ago
What does YTD total need to be = VAR A = [Accounting goal calc-c] VAR B = [Daily Goal] VAR CurrentIndex = SELECTEDVALUE ( Dates[FD2] ) VAR MaxIndex = CALCULATE ( MIN ( Dates[FD2] ), ALLEXCEPT ( Dates, Dates[Date] ) ) RETURN IF ( DAY ( SELECTEDVALUE ( Dates[Day] ) ) = 1 && MONTH ( SELECTEDVALUE ( Dates[Day] ) ) = 1, 0, IF ( CurrentIndex <> BLANK (), A + ( MaxIndex - CurrentIndex ) * B ) )
Hi KW123
Sorry it was too late yesterday I couldn't reply to you. Here is a sample file with the solution jnowing that you will retun back to me with more information that you've been hiding as usual 😉https://www.dropbox.com/t/lzKxh0DxLA056sy7
Result =
VAR A = 1000
VAR B = 10
VAR CurrentIndex = SELECTEDVALUE ( Data[INDEX] )
VAR MaxIndex = CALCULATE ( MAX ( Data[INDEX] ), ALLEXCEPT ( Data, Data[Day].[Month] ) )
RETURN
IF (
CurrentIndex <> BLANK ( ),
A - ( MaxIndex - CurrentIndex ) * B
)- KW1234 years agoHelper V
tamerj1
Thank you very much for your help! I think this is what I am looking for. The thing is, when I change the month, it isn't starting from the months goal ($1000 in the example case) How would we get it to start at the current months goal? It looks as though February is starting at the VAR B amount for that month- KW1234 years agoHelper V
JANUARY
VAR A= $1000
VAR B= $10Day INDEX What I am trying to calculate 1 Saturday 1 $810 2 Sunday 3 Monday (HOLIDAY) 1 $810 4 Tuesday 2 $820 5 Wednesday 3 $830 6 Thursday 4 $840 7 Friday 5 $850 8 Saturday 5 $850 9 Sunday 10 Monday 6 $860 11 Tuesday 7 $870 12 Wednesday 8 $880 13 Thursday 9 $890 14 Friday 10 $900 15 Saturday 10 $900 16 Sunday 17 Monday HOLIDAY 10 $900 18 Tuesday 11 $910 18 Wednesday 12 $920 20 Thursday 13 $930 21 Friday 14 $940 22 Saturday 14 $940 23 Sunday 24 Monday 15 $950 25 Tuesday 16 $960 26 Wednesday 17 $970 27 Thursday 18 $980 28 Friday 19 $990 29 Saturday 19 $990 30 Sunday 31 Monday 20 $1000 (I only know this number, I need to work in reverse from here) FEBRUARY
VAR A=$2500
VAR B= $50Day INDEX What I am trying to calculate 1 Tuesday 1 $650 2 Wednesday 2 $700 3 Thursday 3 $750 4 Friday 4 $800 5 Saturday 4 $800 6 Sunday 7 Monday 5 $850 8 Tuesday 6 $900 9 Wednesday 7 $950 10 Thursday 8 $1000 11 Friday 9 $1050 12 Saturday 9 $1050 13 Sunday 14 Monday 10 $2000 15 Tuesday 11 $2050 16 Wednesday 12 $2100 17 Thursday 13 $2150 18 Friday 14 $2200 18 Saturday 14 $2200 20 Sunday 21 Monday HOLIDAY 14 $2200 22 Tuesday 15 $2250 23 Wednesday 16 $2300 24 Thursday 17 $2350 25 Friday 18 $2400 26 Saturday 18 $2450 27 Sunday 28 Monday 19 $2500
In my report, instead of starting at "$650" it's starting at "$50" Does that make sense?