Create a column with date end

Hi, Anonymous 

According to your description and the sample picture, I can clearly understand your requirement, you can achieve this using two calculated columns, you can try these calculated columns:

Rank = RANKX(FILTER('Table',[id]=EARLIER([id])),[Date],,ASC,Dense)

Date end =

var _idmaxdate=CALCULATE(MAX([Date]),FILTER(ALL('Table'),[id]=EARLIER([id])))

var _statusmaxdate=CALCULATE(MAX([Date]),FILTER(ALL('Table'),[id]=EARLIER([id])&&[status]=EARLIER([status])))

var _nextdate=CALCULATE(MAX([Date]),FILTER(ALL('Table'),[id]=EARLIER([id])&&[Rank]=EARLIER([Rank])+1))

return

SWITCH(

    TRUE(),

    [Date]=_idmaxdate,"Today",

    [Date]=_statusmaxdate,FORMAT(_nextdate,"yyyy-mm-dd hh:mm:ss"),

    BLANK())

And make sure the data type of the column to be “Text”, you can get what you want, like this:

You can download my test pbix file below

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.