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
Icon for Community Champion rankCommunity Champion
10 years ago

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

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's avatar
    ankitpatira
    Icon for Community Champion rankCommunity Champion
    10 years ago

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

      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's avatar
        ankitpatira
        Icon for Community Champion rankCommunity 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.