Create a column with date end

[Anonymous]

Hi guys,
I have a table like:

I need to create a column with date end for each status like this:

Using Dax or M. The date end is the date of beginning of the next status.
Can someone help me? My problem is the format the date because I need to have the date and time. 

Thanks

[v-robertq-msft · Accepted solution]

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.

[selimovd]

Hey @Anonymous ,

I would happily help you, but where do you get the end date from?

Best regards

Denis

[Anonymous]

The date end is the date of beginning of the next status.
Thanks