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 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

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)

6 Replies

  • 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.