Running total with filter

[Anonymous]

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 

[Icey]

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

[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.

[Ashish_Mathur]

Hi,

Show the end result that you are expecting.

[amitchandak]

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.

[Greg_Deckler]

Is there a values column?

[Anonymous]

Didn't understand that. As in?