Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Create a column with date end

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

Need Help
  • v-robertq-msft's avatar
    v-robertq-msft
    5 years ago

    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.

3 Replies

  • selimovd's avatar
    selimovd
    Most Valuable Professional
    5 years ago

    Hey Anonymous ,

     

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

     

    Best regards

    Denis

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

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

  • v-robertq-msft's avatar
    v-robertq-msft
    Community Support
    5 years ago

    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.