Nested IF statement dates

[Anonymous]

Hi,

I am trying to get the most recent date from 2 columns. If the result date is in 2018 then it must be transformed to 1-1-2019. this part to transform from 2018 to 2019 is where i get stuck.

i hope some can help me.

Colum 1: Startdate (Startdatum in indicatie (301)

record 1: 1-12-2018

record 2: 4-1-2019

record 3: 6-2-2019

record 4: 20-2-2019

Colomn 2: Real Startdate (Werkelijke startdatum zorg)

record 1: 25-12-2018

record 2: 3-1-2019

record 3: null

record 4: 21-2-2019

result i am looking for:

record 1: column 2 and must be transformed to 1-1-2019

record 2: column 1

record 3: column 1

record 4: column 2

I have already tried this, but i've got stucked at the transform to 1-1-2019 part:

Startdatum 2019 = IF(ISBLANK(page[Werkelijke startdatum zorg].[Date]);page[Startdatum in indicatie (301)].[Date];

IF(page[Werkelijke startdatum zorg].[Date] < page[Startdatum in indicatie (301)].[Date];page[Startdatum in indicatie (301)].[Date];

IF(page[Werkelijke startdatum zorg].[Date] >= page[Startdatum in indicatie (301)];page[Werkelijke startdatum zorg].[Date];

if the result of the above  creates a date in 2018 then it must be transformed to 1-1-2019.

IF(page[Werkelijke startdatum zorg].[Date]  < 1-1-2019;1-1-2019;IF(page[Startdatum in indicatie (301)].[Date] < 1-1-2019;1-1-2019;0)))))

[novilpawar · Accepted solution]

@Anonymous  hi,

Create one calculated column.

please change my columns name with yours.

""

CC = 

var max_date= IF([Colum 1]>[Colum 2] , [Colum 1] , [Colum 2])

var upd_date = IF(YEAR(max_date) < 2019 || max_date = null , DATE(2019,1,1) , max_date)

return

upd_date

""

Regards,

Novil