Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Date Difference with a switch between two dates and One date and Today

I have two dates on my table. These two dates represent the start date and the End date of projects. I have a measure called Days elapsed that looks at the number of days that have passed. It is as below.

Days Elapsed = 
CALCULATE(DATEDIFF(
    SELECTEDVALUE(Projects[entry_date]),
    SELECTEDVALUE(Projects[date_completed]),DAY
    )

)

 However, some projects are not done hence  I need to show that these projects have not ended. Actually, The measure Days Elapsed should only have projects that have not been completed but the logic is not working out. There is a column that shows if a project has been completed or not. Its called 

projects[Status]



In short, I am trying to find the date difference for projects not completed, and today. Any ideas are highly welcome.


Need Help
  • goncalogeraldes's avatar
    goncalogeraldes
    5 years ago

    Hey thereAnonymous, how about this one? If this one does not work try with a SWITCH() instead of an IF()!

     

    IF( _status in {"completed", "removed"}, DATEDIFF ( _start_date, _end_date, DAY ), 0 )

     

    Hope this answer solves your problem! If you need any additional help please tag me in your reply.
    If my reply provided you with a solution, pleased mark it as a solution ✔️ or give it a kudoe 👍
    Thanks!

    Best regards,
    Gonçalo Geraldes

3 Replies

  • goncalogeraldes's avatar
    goncalogeraldes
    Icon for Super User rankSuper User
    5 years ago

    Hello there! I would do something in the lines of the following! 

    Days Elapsed = 
var _status = SELECTEDVALUE(projects[Status])
var _start_date = SELECTEDVALUE(projects[entry_date])
var _end_date = SELECTEDVALUE(projects[date_completed])

return
IF(
      _status = "Not completed",
      DATEDIFF(_start_date, _end_date, DAY),
      0)

     

    Hope this answer solves your problem! If you need any additional help please tag me in your reply.
    If my reply provided you with a solution, pleased mark it as a solution ✔️ or give it a kudoe 👍
    Thanks!

    Best regards,
    Gonçalo Geraldes

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hey goncalogeraldes , it almost works, however, I have other categories in 

       

      project[Status]

       

      I have completed, in-progress, not started, removed, on-hold, returned. 
      So I wanted to get rid of completed projects and removed projects from the measure. I have implemented it as the following below but still some projects seeing pass.

       

        IF ( _status <> "completed" || _status <> "removed" , DATEDIFF ( _start_date, _end_date, DAY ), 0 )

       

        Assist in this please.

      • goncalogeraldes's avatar
        goncalogeraldes
        Icon for Super User rankSuper User
        5 years ago

        Hey thereAnonymous, how about this one? If this one does not work try with a SWITCH() instead of an IF()!

         

        IF( _status in {"completed", "removed"}, DATEDIFF ( _start_date, _end_date, DAY ), 0 )

         

        Hope this answer solves your problem! If you need any additional help please tag me in your reply.
        If my reply provided you with a solution, pleased mark it as a solution ✔️ or give it a kudoe 👍
        Thanks!

        Best regards,
        Gonçalo Geraldes