Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
OwenAuger
Super User
Super User

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.


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

7 REPLIES 7
OwenAuger
Super User
Super User

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.


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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
Resolver II
Resolver II

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

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

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.

 

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
If this post helps, please consider accept as solution to help other members find it more quickly.

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.