Find the first occurance by time

[DavidK85]

Hi,

I have a data set where I am trying to find the first and last occurance of an id number by time, but I can only find solutions by date:

As you can see from the data set above, all the the changes to 'PESpread' occur within the same date but at different times. I am looking to find the 'PESpread' value that occurred first and last within the day for each dealId then find the difference. So I would like to create a table in my dashboard that will say something like this:

DealID	Borrower Name	Pricing Date	First PESpread	Last PESpread	Difference
13900134	Hong Kong Airport Authority	02/01/2024	4.2	3.83	0.37

Is this possible?

Thanks

[amitchandak · Accepted solution]

@DavidK85 , In case you need a column

First time =

var _time = minx(filter(Table, [Borrower.Name] = earlier( [Borrower.Name]) && [Pricing Date] = earlier([Pricing Date]) ), [PE Time])

rerurn

minx(filter(Table, [Borrower.Name] = earlier( [Borrower.Name]) && [Pricing Date] = earlier([Pricing Date]) && [PE Time] =_time) , [PE Spread])

Last time =

var _time = Maxx(filter(Table, [Borrower.Name] = earlier( [Borrower.Name]) && [Pricing Date] = earlier([Pricing Date]) ), [PE Time])

rerurn

Maxx(filter(Table, [Borrower.Name] = earlier( [Borrower.Name]) && [Pricing Date] = earlier([Pricing Date]) && [PE Time] =_time) , [PE Spread])

In case you need Measures

First time =

var _time = minx(filter(allselected(Table), [Borrower.Name] = max( [Borrower.Name]) && [Pricing Date] = max([Pricing Date]) ), [PE Time])

rerurn

minx(filter(Table, [Borrower.Name] = max( [Borrower.Name]) && [Pricing Date] = max([Pricing Date]) && [PE Time] =_time) , [PE Spread])

Last time =

var _time = Maxx(filter(allselected(Table), [Borrower.Name] = max( [Borrower.Name]) && [Pricing Date] = max([Pricing Date]) ), [PE Time])

rerurn

Maxx(filter(Table, [Borrower.Name] = max( [Borrower.Name]) && [Pricing Date] = max([Pricing Date]) && [PE Time] =_time) , [PE Spread])

Latest Date
https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee8...