Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
107 | |
70 | |
70 | |
43 |
User | Count |
---|---|
148 | |
106 | |
104 | |
89 | |
65 |