Find value for first and last date by person

[dpfavand]

Hi,

I am trying to create a measure that shows a value from a row with the firstdate for a person, and then another to show the value corresponding to the lastdate. Finally, I'd like to be able subtract the first from the last within the current context as chosen by a year slicer.

For example, with the following source table:

DATE        NAME    VALUE
2015-01-01  Daniel  60
2015-01-01  Justin  50
2015-06-01  Daniel  30
2015-06-01  Justin  90
2016-01-01  Daniel  20
2016-06-01  Daniel  10

I'd like to create a report table like:

Name   FirstValue LastValue ValueChange
Daniel 60         10        -50
Justin 50         90        40

I'd also like to be able to show the average change in a card visualization, ie -5 in this case. And eventually the report should filter by several slicers (not just date).

I tried this forumula for the FirstValue measure:

LOOKUPVALUE('Table1'[Value],'Table1'[Date],FIRSTDATE('Table1'[Date]))

And ValueChange = LastValue - FirstValue

However, it only works when there is one record for a particular date - ie if I use a slicer to filter by year, 2015 doesn't work, but 2016 does.

The error is: Calculation error in measure 'Table1'[FirstValue]: A table of multiple values was supplied where a single value was expected.

Does anyone have a suggestion on how to achieve this functionality? This seems to be particularly hard to search for, although I'm sure someone else has needed/done this.

Thank you in advance!

[OwenAuger · Accepted solution]

Hi @dpfavand,

Why not just use FIRSTDATE and LASTDATE in measures like this:

FirstValue = 
CALCULATE ( MIN ( Table1[Value] ), FIRSTDATE ( Table1[Date] ) )

LastValue = 
CALCULATE ( MIN ( Table1[Value] ), LASTDATE ( Table1[Date] ) )

ValueChange = 
[LastValue] - [FirstValue]

The above measures ignore aggregation over names. If you wanted to sum FirstValue and LastValue over names then use:

FirstValue (sums over names) = 
SUMX (
	VALUES( Table1[Name] ),
	CALCULATE ( MIN ( Table1[Value] ), FIRSTDATE ( Table1[Date] ) )
)

LastValue (sums over names) = 
SUMX (
	VALUES( Table1[Name] ),
	CALCULATE ( MIN ( Table1[Value] ), LASTDATE ( Table1[Date] ) )
)

ValueChange (sums over Names) = 
[LastValue (sums over names)] - [FirstValue (sums over names)] 

It might be nice to have a separate date table but you don't need one for this example. MIN is arbitrarily used to get a single value.

Sample PBIX here.

[Anonymous]

Hi @OwenAuger  - Thanks for the update. This solution works !! 

I got an error related to duplicate value : FIRSTDATE. And i solved it with format type and your query.

Regs,

Vivid

[jbocachica]

Hi, you must create some aditional columns.

First Date = calculate(min(Table1[Date]), filter(Table1, Table1[Name] = EARLIER(Table1[Name])))

Last Date = calculate(max(Table1[Date]), filter(Table1, Table1[Name] = EARLIER(Table1[Name])))

FirstValue = if(Table1[Date] = Table1[First Date], Table1[Value], 0)

LastValue = if(Table1[Date] = Table1[Last Date], Table1[Value], 0)

Then add a meassure Value Change = sum(Table1[LastValue]) - sum(Table1[FirstValue]) 

I'm sending you the pbix https://mliworkers.blob.core.windows.net/jboca/First%20and%20Last%20Dates.pbix

Let me know if you have any issue.

Regards

[dpfavand]

Thanks, @jbocachica, for the response.

Unfortunately that doesn't allow one to filter using a slicer, etc., to see change within a year. For example, using your file, adding a slicer on "Date" and then selecting 2015 results in this:

With 2015 selected, the [Value Change] for Daniel should be -30 (from FirstValue 60 to LastValue 30).

[dpfavand]

I think I've gotten some things fixed, but still unable to show an average of Value Change

Link to an updated .pbix

https://1drv.ms/u/s!AEW2ZaQCmXWXjt01

This uses the following measures:

FirstDate = Min(Table1[Date])

LastDate = MAX(Table1[Date])

FirstValue = Calculate(Values(Table1[Value]), Sample(1,'Table1','Table1'[Date],ASC))

LastValue = Calculate(Values(Table1[Value]), Sample(1,'Table1','Table1'[Date],DESC))

ValueChange = [LastValue]-[FirstValue]

Then when I filter with the slicer, each row updates appropriately. However, I can't do averages of the change as far as I can tell.

[v-shex-msft]

Hi Dpfavand,

Based on jbocachica’s formula, I modified his code to fix the issue, you can refer to below code:

Calculate columns:

First Date(C) = calculate(min(Table1[Date]), filter(Table1, Table1[Name] = EARLIER(Table1[Name])&&YEAR(Table1[Date])=YEAR(EARLIER(Table1[Date]))))

Last Date(C) = calculate(max(Table1[Date]), filter(Table1, Table1[Name] = EARLIER(Table1[Name])&&YEAR(Table1[Date])=YEAR(EARLIER(Table1[Date]))))

FirstValue(C) = if(Table1[Date] = Table1[First Date(C)], Table1[Value], 0)

LastValue(C) = if(Table1[Date] = Table1[Last Date(C)], Table1[Value], 0)

Screenshots:

 

Regards,

Xiaoxin Sheng

[dpfavand]

Thanks @v-shex-msft, for your suggestion. I tried it out, and found that it gave the wrong values if no year were selected. 

In the screenshot below, taken from the file you kindly uploaded, Daniel should have 60 for FirstValue and 10 for LastValue: