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 need to do a running total calculation for the below
Task | Due Date | Actual Completion Date |
A1 | 1/15/2018 | 2/4/2018 |
A2 | 2/16/2018 | 3/7/2018 |
A3 | 3/20/2018 | 1/15/2018 |
A4 | 4/21/2018 | 2/16/2018 |
A5 | 5/23/2018 | 9/28/2018 |
A6 | 6/24/2018 | 10/30/2018 |
A7 | 7/26/2018 | 12/1/2018 |
A8 | 8/27/2018 | 9/9/2018 |
A9 | 9/28/2018 | |
A10 | 10/30/2018 | |
A11 | 12/1/2018 |
Conditions :
1) Task should be included in the Running total if the Actual completion date>"Due .Date" or the Actual completion date is null
2) For instance, Task A5 has a due date as 5/23/2018 but task completed on 9/28/2018 , it should be calculated untill the Sep-2018 as a backlog, whereas Task A3 should not be in the Running total
Thanks
Hi @Anonymous ,
Is this problem solved?
If it is solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.
If not, please let me know.
Best Regards
Icey
Hi @Anonymous ,
Please check if this is what you want:
1. Assume that all tasks are assigned on 2018/1/1.
2. Create a Calendar table.
Calendar = CALENDAR(DATE(2018,1,1),DATE(2018,12,31))
3. Create relationship.
4. Create measures like so:
Measure =
VAR CurrentDate =
MAX ( 'Calendar'[Date] )
VAR CurrentDueDate =
MAX ( 'Table'[Due Date] )
VAR CurrentActualDate =
IF (
MAX ( 'Table'[Actual Completion Date] ) = BLANK (),
TODAY (),
MAX ( 'Table'[Actual Completion Date] )
)
VAR DD =
IF (
CurrentDueDate <= CurrentActualDate
&& CurrentDate <= CurrentDueDate,
CurrentDueDate,
IF (
CurrentDueDate <= CurrentActualDate
&& CurrentDate >= CurrentDueDate
&& CurrentDate <= CurrentActualDate,
CurrentActualDate,
IF (
CurrentDueDate > CurrentActualDate,
CurrentActualDate,
IF ( CurrentActualDate = TODAY (), CurrentDate )
)
)
)
VAR Is1 =
IF ( DD = CurrentDate, 1, IF ( DD <= MAX ( 'Calendar'[Date] ), 1, 0 ) )
RETURN
Is1
Running total =
CALCULATE ( COUNT ( 'Table'[Task] ), ALLSELECTED ( 'Table' ) )
- CALCULATE (
SUMX ( 'Calendar', [Measure] ),
FILTER (
ALLSELECTED ( 'Calendar' ),
'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
)
)
For more details, please check the attached PBIX file.
If I understand it wrong, please let me know.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Show the end result that you are expecting.
Refer this blog to discuss similar stuff :https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
Appreciate your Kudos.
Is there a values column?
Didn't understand that. As in?
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 |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |