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.
Hi.. I tried my best but I am stuck and requesting your help for the following from given table.
1. Planned Duration which I can calcualte using= DATEDIFF(MIN('Project Details Sheet'[Planned Start Date]), MAX('Project Details Sheet'[Planned Completion date]),DAY)+0
2. Actual Duration when Task is not in selection, means Total of Actual Duration as of today, but if All tasks has dates than it should not take Today() in calculations and instead take the last date (of K).
3. Actual Duration when Task is focus
4. Actual Duration should show "In Progress" if task has blank completion
5. Duration Remaining in all the above cases by taking the planned completion date in consideration
6. % of Duration , +- in case of days are more than the planned.
- These are Cards
Tasks | Actual Start Date | Actual Completion Date | Planned Start Date | Planned Completion Date |
A | 25/08/20 | 05/09/20 | 09/01/20 | 09/07/20 |
B | 15/09/20 | 15/02/21 | 29/09/20 | 11/07/21 |
C | 07/12/20 | 15/03/21 | 14/11/20 | 22/01/21 |
D | 20/12/20 | 18/04/21 | 14/11/20 | 22/01/21 |
E | 01/02/21 | 11/03/21 | 01/02/21 | 11/03/21 |
F | 09/09/20 | 09/02/21 | 19/09/20 | 28/09/20 |
G | 28/11/20 | 03/01/21 | 18/10/20 | 25/11/20 |
H | 07/04/21 | 16/05/21 | 07/04/21 | 10/05/21 |
I | 11/04/21 | 11/04/21 | 05/09/21 | |
J | 17/03/21 | 29/03/21 | 21/04/21 | 02/05/21 |
K | 07/06/21 | 18/05/21 | 10/09/21 |
Thanks a lot.
Solved! Go to Solution.
You may want to look into using the COALESCE function.
For your "actual completion date" being blank scenario it would say
Actual Completion = COALESCE([Actual Completion Date],TODAY())
meaning that if the date is missing it would be supplemented by the current date.
word of warning! TODAY() depends on either user interaction (if used in a measure) or frequent/daily dataset refreshes (if used in a calculated column). It is not automatic!
You may want to look into using the COALESCE function.
For your "actual completion date" being blank scenario it would say
Actual Completion = COALESCE([Actual Completion Date],TODAY())
meaning that if the date is missing it would be supplemented by the current date.
word of warning! TODAY() depends on either user interaction (if used in a measure) or frequent/daily dataset refreshes (if used in a calculated column). It is not automatic!
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 |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |