Forum Discussion

ashishoza12345's avatar
ashishoza12345
Icon for Helper I rankHelper I
3 years ago
Solved

Create a new column with previous date after considering 2 separate ID's

Hi Folks,

 

I need your urgent help on creating a new column "Next Start Date" where it picks up the date from the next row "Start Date" as shown below:

 

 

I tried to create a new column using this DAX but I am getting errors on few dates:

 

Next_Start_date = calculate(MIN(data3[start_dt]), FILTER(data3, data3[pass_id] = EARLIER(data3[pass_id]) && data3[id]>earlier(data3[id])))
 
 

I have attached the PBI on the drive. Please access it using this link

 

Thanks in Advance!

 

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi ashishoza12345 ,

     

    You can refer to the following steps:

    (1) My test data is the same as yours.

    (2) We can click "New Column" to create two calculate columns:

    Next Start date = 
var a=CALCULATE (
    MIN ( 'data3'[Start Date]),
    FILTER (
        'data3',
        'data3'[Start Date]> EARLIER ( 'data3'[Start Date])
             )
    )
return IF(a<>BLANK(),a,BLANK())
    Next Start date by pass id = 
var a=CALCULATE (
    MIN ( 'data3'[Start Date]),
    FILTER (
        'data3',
        'data3'[Start Date]> EARLIER ( 'data3'[Start Date]) &&
        'data3'[Pass_id] = EARLIER(data3[Pass_id])
             )
    )
return IF(a<>BLANK(),a,BLANK())

    (3) Then we can realize your needs:

    Best Regards,

    Neeko Tang

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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi ashishoza12345 ,

     

    You can refer to the following steps:

    (1) My test data is the same as yours.

    (2) We can click "New Column" to create two calculate columns:

    Next Start date = 
var a=CALCULATE (
    MIN ( 'data3'[Start Date]),
    FILTER (
        'data3',
        'data3'[Start Date]> EARLIER ( 'data3'[Start Date])
             )
    )
return IF(a<>BLANK(),a,BLANK())
    Next Start date by pass id = 
var a=CALCULATE (
    MIN ( 'data3'[Start Date]),
    FILTER (
        'data3',
        'data3'[Start Date]> EARLIER ( 'data3'[Start Date]) &&
        'data3'[Pass_id] = EARLIER(data3[Pass_id])
             )
    )
return IF(a<>BLANK(),a,BLANK())

    (3) Then we can realize your needs:

    Best Regards,

    Neeko Tang

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

  • ashishoza12345's avatar
    ashishoza12345
    Icon for Helper I rankHelper I
    3 years ago

    Hello Anonymous ,

     

    Thank you so much for your repsonse.
    It worked for me.

     

     

    Kind Regards