Forum Discussion
Difference between date culumns with an IF like statement
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)
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.
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?
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.
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)