Difference between date culumns with an IF like statement

[khalidmadih]

Hey guys,

I am using PowerBi in my company to create some dashboards, and I need to incorporate a calculated column in my data set that calculates the following :

1/ The difference in Days between column [StartDate] and [EndDate]

2/ However if the [EndDate] row is empty, it should use (Today) as a date

I appreciate the help.

Regards,

Khal

[ankitpatira]

@khalidmadih Below is DAX query that you want to use to create new calculated column.

NewColumn = DATEDIFF(YOURTABLE[startdate],IF(ISBLANK(YOURTABLE[enddate]), TODAY(), YOURTABLE[enddate]),DAY)

[khalidmadih]

Thank you for looking into this!

I tried your solution but I am getting the following error message "In DATEDIFF function, the start date cannot be greater than the end date".

For reference here is what I have enetered:

Days Active = DATEDIFF('Member Profile'[Original Member Effective Date],IF(ISBLANK('Member Profile'[Terminated Member Date]), TODAY(), 'Member Profile'[Terminated Member Date]),DAY)

[ankitpatira]

@khalidmadih As error suggests, you need to reverse your columns so your DAX would be,

Days Active = DATEDIFF('Member Profile'[Terminated Member Date],IF(ISBLANK('Member Profile'[Original Member Effective Date]), TODAY(), 'Member Profile'[Original Member Effective Date]),DAY)

However ensure that if first column has a date which is higher than date in second column then it is the case for all the values, otherwise query will fail.

[khalidmadih]

The 1st formula is the correct one. However it looks like there is a chance that the start date could be equal or greater than the end date. Any other way to go around this issue?

[ankitpatira]

@khalidmadih ok. Do this instead,

NewColumn = (YOURTABLE[end] - YOURTABLE[start]) * 1.

This will work regardless of dates are gretaer or lower in either columns and will give you number accordingly either in positive or negative.

[khalidmadih · Accepted solution]

Again..thank you very much for your help solving this.

I finally approached it a little bit differently, by eliminating the data rows from the logic where the "start date" was greater than the "end date". I simply added another IF statement to put a 0 in that row if that condition is true.

Here is the new formula, hopefully it will help other people with the same issue:

NewColumn = IF('Table'[StartDate] <= IF(ISBLANK('Table'[EndDate]),TODAY(),'Table'[EndDate]) , DATEDIFF('Table'[StartDate], if(ISBLANK('Table'[EndDate]),TODAY(),'Table'[EndDate]),DAY), 0)