Forum Discussion

DavidK85's avatar
DavidK85
Frequent Visitor
2 years ago
Solved

Find the first occurance by time

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 ...
Need Help
  • amitchandak's avatar
    amitchandak
    2 years ago

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

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
2 years ago

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