cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dpfavand
Frequent Visitor

Find value for first and last date by person

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!

1 ACCEPTED SOLUTION

Accepted Solutions
OwenAuger Community Champion
Community Champion

Re: Find value for first and last date by person

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.



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

6 REPLIES 6
jbocachica Advocate III
Advocate III

Re: Find value for first and last date by person

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

John Bocachica
Senior BI Consultant
Information Workers
http://www.iwco.co
dpfavand
Frequent Visitor

Re: Find value for first and last date by person

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:

 

powerbi-slicer-issue.png

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

Highlighted
dpfavand
Frequent Visitor

Re: Find value for first and last date by person

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.

 

Community Support
Community Support

Re: Find value for first and last date by person

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:

 Capture.PNG

 

Capture2.PNG

Capture3.PNG

 

 

Regards,

Xiaoxin Sheng

 

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
dpfavand
Frequent Visitor

Re: Find value for first and last date by person

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:

powerbi-tracker-wrong-numbers2.png

 

OwenAuger Community Champion
Community Champion

Re: Find value for first and last date by person

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.



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors