cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dpfavand Frequent Visitor
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 Super Contributor
Super Contributor

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 Member
Member

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

dpfavand Frequent Visitor
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 Team
Community Support Team

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
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 Super Contributor
Super Contributor

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
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors