Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Datagulf
Responsive Resident
Responsive Resident

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.


1 ACCEPTED SOLUTION

Hey there@Datagulf, 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

View solution in original post

3 REPLIES 3
goncalogeraldes
Super User
Super User

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

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.

Hey there@Datagulf, 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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.