Forum Discussion

khalidmadih's avatar
khalidmadih
Frequent Visitor
10 years ago
Solved

Difference between date culumns with an IF like statement

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 betwe...
Need Help
  • khalidmadih's avatar
    khalidmadih
    10 years ago

    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)

ankitpatira's avatar
ankitpatira
Community Champion
10 years ago

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's avatar
khalidmadih
Frequent Visitor
10 years ago

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)