Nested IF and DATEDIFF formulas when some date fields are blank

[NRW_admin1]

I would like to count the number of days between two date fields: 'datereceived' and 'datedulymade'. I tried to use the DATEDIFF function, BUT - some of the corresponding dates are the same (e.g. number of days between dates=0) which seems to cause an error, ALSO, some of the 'datedulymade' rows are blank (i.e. process not complete)

I have tried to nest an IF statement in to the DATEDIFF field however the blank date fields seem to be causing an error of the format type and I I am unsure how to get around this.

[Greg_Deckler]

Perhaps use ISBLANK in your IF statement to check for that boundary condition?

https://msdn.microsoft.com/en-us/library/ee634204.aspx

[Sean]

@NRW_admin1 You can use DATEDIFF when the dates are the same (see picture below) but you can't have blanks or negative values (to deal with negatives see link at the bottom)

Num of Days =
IF (
    ISBLANK ( 'Table'[Made] ),
    BLANK (),
    DATEDIFF ( 'Table'[Received], 'Table'[Made], DAY )
)

Also see response here... (although this doesn't deal with Blank may give some ideas)

http://community.powerbi.com/t5/Desktop/How-to-use-DATEDIFF-when-End-Date-is-less-than-Start-Date/m-...

[NRW_admin1]

Problem solved, combined with guidance in the link. Many thanks 🙂

[Eric_Zhang]

@NRW_admin1
It is great to hear the problem got solved.

It would be greatly appreciated of you if  you can post the solution and accepting it as solution can help the people who would have the same problem to find the answer quickly. 🙂

[NRW_admin1]

To workaround the blank values in the date column, I created a new column with a false date (01/01/2025) for all the blanks:

false date (duly made) = IF(ISBLANK(permitapplicationSet[nrw_dulymadedate]), DATE(2025, 1, 1), permitapplicationSet[dulymadedate])

Then I used the formula to calculate number of days taken to duly make the applications:

Days to duly make = SWITCH(TRUE(), permitapplicationSet[datereceived]<permitapplicationSet[dulymadedate], DATEDIFF(permitapplicationSet[datereceived], permitapplicationSet[dulymadedate], DAY), permitapplicationSet[datereceived]>permitapplicationSet[dulymadedate], -1*DATEDIFF(permitapplicationSet[dulymadedate], permitapplicationSet[datereceived], DAY), 0)

This actually returned a blank for all rows with the false date. Which is great although I wasn't expecting that result!

[Nhallquist]

Can you post the formula's that you have tried?