Forum Discussion

lackeyj's avatar
lackeyj
Regular Visitor
2 years ago
Solved

Current Level and Next Due Date

Hello! I am looking to automatically calculate the current level (all Sub-Tasks have to be completed) that I am on and when the next one is due. This is what my table looks like:

 

LevelSub-TaskDue DateStatus
11.101/01/2024Completed
22.106/01/2024Completed
22.207/01/2024Completed
33.109/01/2024Open
33.208/01/2024Completed

 

The results would be: 

Current Completed Level: 2    (working on completing level 3) 

Next Level Due Date: 09/01/2024    (So I need to complete the last task for level 3 by 09/01/2024 even if it is the first sub-task for that level)

 

Thank you!!!

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi lackeyj ,

    I create a table as you mentioned.

    Next I create a calculated column named All_Tasks_Completed.

    All_Tasks_Completed = 
VAR CurrentLevel = 'Table'[Level]
RETURN
IF(
    COUNTROWS(
        FILTER(
            'Table',
            'Table'[Level] = CurrentLevel && 'Table'[Status] <> "Completed"
        )
    ) = 0,
    1,
    0
)

    Then I create a measure named Current Completed Level.

    Current Completed Level = 
MAXX(
    FILTER(
        'Table',
        'Table'[All_Tasks_Completed] = 1
    ),
    'Table'[Level]
)

    Finally I create a measure named Next Level Due Date.

    Next Level Due Date = 
VAR NextLevel = [Current Completed Level] + 1
RETURN
CALCULATE(
    MAX('Table'[Due Date]),
    FILTER(
        'Table',
        'Table'[Level] = NextLevel
    )
)

     

     

     

    Best Regards

    Yilong Zhou

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi lackeyj ,

    I create a table as you mentioned.

    Next I create a calculated column named All_Tasks_Completed.

    All_Tasks_Completed = 
VAR CurrentLevel = 'Table'[Level]
RETURN
IF(
    COUNTROWS(
        FILTER(
            'Table',
            'Table'[Level] = CurrentLevel && 'Table'[Status] <> "Completed"
        )
    ) = 0,
    1,
    0
)

    Then I create a measure named Current Completed Level.

    Current Completed Level = 
MAXX(
    FILTER(
        'Table',
        'Table'[All_Tasks_Completed] = 1
    ),
    'Table'[Level]
)

    Finally I create a measure named Next Level Due Date.

    Next Level Due Date = 
VAR NextLevel = [Current Completed Level] + 1
RETURN
CALCULATE(
    MAX('Table'[Due Date]),
    FILTER(
        'Table',
        'Table'[Level] = NextLevel
    )
)

     

     

     

    Best Regards

    Yilong Zhou

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.