Find middle date in between first and last date, and put in column

[Anonymous]

I have a table like this:

id	timestamp	temperature	first date - id	last date - id	middle date - id	first, last, middle
1	1/1/20 1:00 AM	14	TRUE	FALSE		First
1	1/1/20 2:00 PM	12.4	FALSE	FALSE		na
1	1/1/20 8:00 AM	16.3	FALSE	FALSE		na
1	1/2/20 1:00 AM	14.2	FALSE	FALSE		na
1	1/2/20 2:00 PM	15.9	FALSE	FALSE		na
1	1/2/20 8:00 AM	14.5	FALSE	FALSE		na
1	1/3/20 1:00 AM	13.2	FALSE	FALSE		na
1	1/3/20 2:00 PM	19.3	FALSE	TRUE		Last
1	1/3/20 8:00 AM	16.9	FALSE	FALSE		na
2	1/1/20 1:00 AM	14.3	TRUE	FALSE		First
2	1/1/20 2:00 PM	12.4	FALSE	FALSE		na
2	1/1/20 8:00 AM	16.3	FALSE	FALSE		na
2	1/2/20 1:00 AM	14.2	FALSE	FALSE		na
2	1/2/20 1:00 PM	15.9	FALSE	FALSE		na
2	1/2/20 8:00 AM	14.5	FALSE	FALSE		na
2	1/3/20 2:00 AM	13.2	FALSE	FALSE		na
2	1/3/20 1:00 PM	13.5	FALSE	TRUE		Last
2	1/3/20 8:00 AM	16.9	FALSE	FALSE		na

I have calculated the first and laste date using this formula: IF(tabledb[timestamp]=CALCULATE(MIN(tabledb[timestamp]),ALLEXCEPT('tabledb', 'tabledb'[id])),True)

How can I get the middle date in a format like last and first, and then create a new column to show first, middle and last date for every id, like shown in table above for first and last date.

Thanks a lot.

[V-pazhen-msft · Accepted solution]

@Anonymous 

I recommend you first ranked the timestamp for each id. Then use MEDIAN to get the middle rank for each id.

Rank= RANKX(FILTER(Tabledb,[id]=EARLIER(Tabledb[id])),Tabledb[timestamp],,ASC,Dense)

firstmidlast = 
IF(Tabledb[Rank]=MIN(Tabledb[Rank]),"first",IF(Tabledb[Rank]=MAX(Tabledb[Rank]),"last",IF(Tabledb[Rank]=MEDIAN(Tabledb[Rank]),"mid")))

Best,
Paul

[amitchandak]

create rank inside each id.  crease dense rank

refer -https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures

create rank as column.

Now using that you can use min and max of rank try to find middle rank and use that.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601