Creating an Index in Power BI - help with calculating based on current row value

[wolfboyjan]

I'm trying to create an Index of times, to show whether individual runners are getting faster or slower over time, with a runner's first time as 100.0 and any decrease in future times being expressed as a reduction, e.g. a 10% faster time would be expressed as 90.0, and so on.

I understand how to achieve this in a couple of ways using Excel, with some combination of MINIFS and pivot tables, but I'd like to do this in Power BI.

I thought it would be quite straightforward to create a column that shows a runners first time and then to use this to further calculate the Index value (time / first time), but I don't seem to be able to achieve this.

Here's an extract of the "data" table where the information is held:

event_name	event_date	event_number	runner_position	runner_name	runner_gender	runner_time
Lyme Park parkrun	25/02/2023	390	1	Runner a	Male	00:19:20
Lyme Park parkrun	25/02/2023	390	2	Runner b	Male	00:20:32
Lyme Park parkrun	25/02/2023	390	3	Runner c	Male	00:20:40
Lyme Park parkrun	25/02/2023	390	4	Runner d	Male	00:21:45
Lyme Park parkrun	04/03/2023	391	1	Runner e	Male	00:18:46

And here's the custom column I have tried to create, which currently only works when I manually enter a runner's name ("John Smith" in the example below):

First_time = LOOKUPVALUE(data[runner_time],data[runner_name],"John Smith",data[event_date],data[First_week])

In Excel I would simply refer to the specific cell within the current row of the "runner_name" column, but I realise this isn't the right approach in Power BI. I have searched online and tried to use the EARLIER function, but receive error messages which I have been unable to resolve.

Any help with this and/or how to produce the Index I described would be really appreciated!

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[wolfboyjan]

No problem, here's a one drive link to some sample data: example_data.csv 

I'd like to create:

1. A column that shows the earliest date a runner appears in the data (dd/mm/yy)
2. A column that shows the time that the runner achieved on that earliest date (h:mm:ss)
3. An index column that shows the difference between their earliest time and their current time (decimal number)

Thanks

[Ashish_Mathur]

Hi,

These calculated column formulas should answer the first 2 questions

Earliest date of runner = CALCULATE(MIN(Data[event_date]),FILTER(Data,Data[runner_name]=EARLIER(Data[runner_name])))

Time at earliest date = LOOKUPVALUE(Data[runner_time],Data[event_date],Data[Earliest date of runner],Data[runner_name],Data[runner_name])

Hope this helps.

[Chris_Kennedy]

Thank you for your help and time. I tried to implement this but received the error: "A table of multiple values was supplied where a single value was expected." when I try to create the Time at earliest date column". 

Unfortunately I can't see any more detail about which column is generating this error. 

[Ashish_Mathur]

You are welcome.  Share the download link of the PBI file.  Show the problem and expected result clearly.

[lbendlin]

have fun analyzing the data

[Chris_Kennedy]

Thanks very much for sharing that solution. Do you know if it's possible to adapt this approach to allow me to chart runner progress over time, rather than the first vs latest run?