cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

tex628 New Contributor
New Contributor

Re: Nested IF statement dates

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

View solution in original post

10 REPLIES 10
tex628 New Contributor
New Contributor

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.

tex628 New Contributor
New Contributor

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.

tex628 New Contributor
New Contributor

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

tex628 New Contributor
New Contributor

Re: Nested IF statement dates

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

View solution in original post

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 284 members 2,761 guests
Please welcome our newest community members: