cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pjverweij Regular Visitor
Regular Visitor

Nested IF statement dates

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)))))

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
novilpawar Frequent Visitor
Frequent Visitor

Re: Nested IF statement dates

@pjverweij  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

View solution in original post

Super User II
Super User II

Re: Nested IF statement dates

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

View solution in original post

10 REPLIES 10
Super User II
Super User II

Re: Nested IF statement dates

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.

novilpawar Frequent Visitor
Frequent Visitor

Re: Nested IF statement dates

@pjverweij  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

View solution in original post

pjverweij Regular Visitor
Regular Visitor

Re: Nested IF statement dates

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

Super User II
Super User II

Re: Nested IF statement dates

Are your columns in date-format?

pjverweij Regular Visitor
Regular Visitor

Re: Nested IF statement dates

@novilpawar  and @tex628 

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

pjverweij Regular Visitor
Regular Visitor

Re: Nested IF statement dates

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.

Super User II
Super User II

Re: Nested IF statement dates

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

pjverweij Regular Visitor
Regular Visitor

Re: Nested IF statement dates

Knipsel.PNG

Super User II
Super User II

Re: Nested IF statement dates

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

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors