Subtracting the "first" value in a column from all entries in that column; "first" by slicer

[pieterhkruger]

I have a table where I want to subtract the "first" value (or record) in a particular column from all entries in that same column, but where "first" is determined by a slicer, and namely by the earliest date.  To explain using a simple example:

Suppose I have the following table:

DATE           COLUMN1

1/1//2018   1

1/2/2018    4

1/3/2018    3

1/4/2018    6

Then, suppose I have slicer on the DATE, and the slicer runs from 1/1/2018 - 1/4/2018, I want to have the equivalent of:

DATE           MEASURE

1/1//2018   0

1/2/2018    3

1/3/2018    2

1/4/2018    5

(i.e.: 1 subtracted from all the values)

Suppose the slicer is set to 1/2/2018 - 1/4/2018, I want to have the equivalent of:

DATE           MEASURE

1/2/2018    0

1/3/2018    -1

1/4/2018    2

(i.e.: 4 subtracted from all the values)

Would this be possible at all?  And if so, how would I go about to create such a measure?

[Greg_Deckler]

Perhaps something along the lines of:

Measure = 
VAR __min = MIN([DATE])
VAR __minValue = MAXX(FILTER('Table',[DATE]=__min),[COLUMN1])
VAR __currentValue = MAX([COLUMN1])
RETURN 
__currentValue - __minValue

[pieterhkruger]

Thanks for the reply. 

Not sure if I am doing something wrong, but I get values of 0 (adding up to a total of 9?).

I've used the following to test the concept:

TableDashboard

New Value =
VAR __min = MIN('TEST DATASET'[DATE])
VAR __minValue = MAXX(FILTER('TEST DATASET',[DATE]=__min),[Value])
VAR __currentValue = MAX([Value])
RETURN
__currentValue - __minValue

Not sure what is happening ...

[Greg_Deckler]

You are going to need a disconnected Date for your slicer. Otherwise, the formula presented will always get the current value of the Date in the row for the __min variable. Row context. If you try without that using ALL then that will give you a different set of problems.

[pieterhkruger · Accepted solution]

SOLVED:

Everything as before, but Measure changed to be as follows:

New Value =
VAR __min = MIN('TEST DATASET DATES'[Date])
VAR __minDate = MINX(FILTER(ALL('TEST DATASET'),[DATE]>=__min),[DATE])
VAR __minValue = MAXX(FILTER(ALL('TEST DATASET'),[DATE]=__minDate),[VALUE])
VAR __currentValue = MAX([Value])
VAR __currentDate = MAX([Date])
RETURN
IF(DATEDIFF(__minDate,__currentDate,DAY)>0||__minDate=__currentDate,__currentValue-__minValue,BLANK()
)

[pieterhkruger]

The closest I got:

So I only need the first two rows to not display; the rest is working fine.  Any suggestions?

This is what I've done:

INPUT TABLE:

SUMMARY TABLE:

TEST DATASET DATES = SUMMARIZE('TEST DATASET','TEST DATASET'[Date])

RELATIONSHIP:

MEASURE:

New Value =
VAR __min = MIN('TEST DATASET DATES'[Date])
VAR __minValue = MINX(FILTER(ALL('TEST DATASET'),[DATE]>=__min),[VALUE])
VAR __currentValue = MAX([Value])
RETURN
__currentValue-__minValue

SLICER:

'TEST DATASET DATES'[dates]

TABLE visualisation:

'TEST DATASET'[dates]

'TEST DATASET'[value]

'TEST DATASET'[new value]

(Removing the ALL-function in the measure's formula above does not work; removing the relationship between the tables also does not work).

[pieterhkruger]

Thanks.  I'll play around with it ...