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

[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

[Anonymous]

@novilpawar  and @tex628 

Power BI displyas an error: Operator or expression NULL is not supported in this context.

[novilpawar]

@Anonymous  hi

Please refer this dax, little change from previous one.

CC =

var max_date= IF([Column1] >[Column2] , [Column1] , [Column2])

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

return upd_date

Regards,

Novil

If I answer you question, please mark my post as solution.

[tex628]

Create two calculated columns:

Column1 = IF([Startdate]>[Real Startdate] , [StartDate] , [Real Startdate])

Column2 = IF(YEAR([Column1]) < 2019 || [Column1] = null , DATE(2019,1,1) , [Column1])

Column2 should give you the result you are looking for.

[Anonymous]

Power BI displays an error: Operator or expression NULL is not supported in this context.

[tex628]

Are your columns in date-format?

[Anonymous]

yes converted from date/time to date, but in format: monday 31 december 2018.

Turned into 31-12-2018 but same error.

if a cell is empty there isn't anything in it. there isn't a null value. its empty.

[tex628]

Can you provide a picture of the error message, the calculation and a few rows from the date columns?

[Anonymous]

[tex628 · Accepted solution]

Replace NULL with BLANK()
Remove the .Date from the date columns.