How to get the next value of another column using dax

[jay_patel]

Hii All

here I have a 'dataset' having column "From Date" but I want another column i.e, "To date" and it should have the next row value of "From Date"

For example I have From-Date

From date
12-11-2019
14-11-2019
17-11-2019
22-11-2019
28-11-2019
30-11-2019
12-01-2020
15-02-2020
24-02-2020

Expected Result

From date  To date
12-11-2019   14-11-2019
14-11-2019   17-11-2019
17-11-2019   22-11-2019
22-11-2019   28-11-2019
28-11-2019   30-11-2019
30-11-2019   12-01-2020
12-01-2020   15-02-2020
15-02-2020   24-02-2020
24-02-2020   31-12-2020 

and the last value of  "To Date" must have 'last date of the year'.

[Anonymous · Accepted solution]

Hi @jay_patel ,

Based on your description, you can do some steps as follows.

1. create an indecx column in Power Query.

2. create a column.

Column =
VAR x1 =
CALCULATE ( MAX ( 'Test'[Index] ), ALL () )
RETURN
IF (
[Index] = x1,
DATE ( YEAR ( MAXX ( FILTER ( ALL ( Test ), [Index] = x1 ), [FromDate] ) ), 12, 31 ),
MAXX ( FILTER ( ALL ( Test ), [Index] = EARLIER ( [Index] ) + 1 ), [FromDate] )
)

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

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

[jay_patel]

Not getting proper values

Got these values: -

FromDate New column
01-02-2019  15-02-2019 00:00
22-01-2020  05-03-2020 00:00
01-02-2019  15-02-2019 00:00
22-01-2020  05-03-2020 00:00
01-02-2019  15-02-2019 00:00
22-01-2020  05-03-2020 00:00
01-02-2019  15-02-2019 00:00
22-01-2020  05-03-2020 00:00
01-02-2019  15-02-2019 00:00
22-01-2020  05-03-2020 00:00

[jay_patel]

It's not working 

FromDate New column
01-02-2019    15-02-2019 00:00
22-01-2020    05-03-2020 00:00
01-02-2019    15-02-2019 00:00
22-01-2020    05-03-2020 00:00
01-02-2019    15-02-2019 00:00
22-01-2020    05-03-2020 00:00
01-02-2019    15-02-2019 00:00
22-01-2020    05-03-2020 00:00
01-02-2019    15-02-2019 00:00
22-01-2020    05-03-2020 00:00

[amitchandak]

@jay_patel , Create a new column like

New column =
var _max = minx(filter(Table, [From date] > earlier([From date])),[From date])
return
if(isblank([From date]), date(year([From date]),12,31),_max)