Calculating Actual Finish Date

prafullchavan1 · November 27, 2022, 3:02pm

I have attached sample data for calculation of actual finish date.
My data hierarchy is as below
Discipline- Sub Discipline- Activity ID
I need to find out plan start, plan finish , actual start & actual finish on sub discipline & discipline level.
I am able to find out plan start , plan finish & actual start with min & max formula.
For actual finish , I have following criteria,
5.a ) Show actual finish date for sub discipline only when all activities are finished.
5.b) Show actual finish date for discipline only when all sub discipline activities are finished

Expected output is mentioned in Result sheet of attached file.

Thanks in advance
Calculating Actual Finish Date.xlsx (257.8 KB)

Harsh · November 27, 2022, 5:59pm

Thank You for posting your query onto the Forum.

In order to achieve the results based on the scenario that you’ve mentioned. Below are the DAX measures for “Sub-Discipline” and “Discipline” alongwith the screenshot of the final results provided for the reference -

Actual Finish Date - Sub Discipline - Data 1 = 
MAXX(
    ADDCOLUMNS(
        FILTER(
            ADDCOLUMNS(
                SUMMARIZE(
                    'Data1' , 
                    'Data1'[Sub Discipline] , 
                    'Data1'[Activity ID] , 
                    'Data1'[Actual Start] , 
                    'Data1'[Actual Finish] ) , 
                "@Ranking_Based_On_Activity_ID" , 
                VAR _Current_Date = 
                'Data1'[Activity ID]

                RETURN
                CALCULATE(
                    COUNTROWS(
                        FILTER( 'Data1' , 
                            'Data1'[Activity ID] > _Current_Date ) ) + 1 , 
                        ALLEXCEPT( 'Data1' , 
                            'Data1'[Sub Discipline] ) ) , 

                "@Ranking_Based_On_Actual_Finish_Date" , 
                VAR _Current_Date = 
                'Data1'[Actual Finish]

                RETURN
                CALCULATE(
                    COUNTROWS(
                        FILTER( 'Data1' , 
                            'Data1'[Actual Finish] > _Current_Date ) ) + 1 , 
                        ALLEXCEPT( 'Data1' , 
                            'Data1'[Sub Discipline] ) ) ) , 
            [@Ranking_Based_On_Activity_ID] = [@Ranking_Based_On_Actual_Finish_Date] &&
            NOT ISBLANK( 'Data1'[Actual Finish] ) ) , 
        "@Max_Actual_Finish_Date" , 
        CALCULATE( MAX( 'Data1'[Actual Finish] ) , 
            ALLEXCEPT( 'Data1' , 'Data1'[Sub Discipline] ) ) ) , 
    [@Max_Actual_Finish_Date] )

Actual Finish Date - Discipline - Data 1 = 
VAR _vTable_1 = 
ADDCOLUMNS(
    SUMMARIZE(
        'Data1' , 
        'Data1'[Discipline] , 
        'Data1'[Sub Discipline] , 
        'Data1'[Activity ID] , 
        'Data1'[Actual Start] , 
        'Data1'[Actual Finish] ) , 
    "@Ranking_Based_On_Activity_ID" , 
    VAR _Current_Date = 
    'Data1'[Activity ID]

    RETURN
    CALCULATE(
        COUNTROWS(
            FILTER( 'Data1' , 
                'Data1'[Activity ID] > _Current_Date ) ) + 1 , 
            ALLEXCEPT( 'Data1' , 
                'Data1'[Sub Discipline] ) ) , 

    "@Ranking_Based_On_Actual_Finish_Date" , 
    VAR _Current_Date = 
    'Data1'[Actual Finish]

    RETURN
    IF( ISBLANK( Data1[Actual Finish] ) , 
        CALCULATE(
            COUNTROWS(
                FILTER( 'Data1' , 
                    'Data1'[Actual Finish] > _Current_Date ) ) + 1 , 
                ALLEXCEPT( 'Data1' , 
                    'Data1'[Sub Discipline] ) ) - 1 , 
        CALCULATE(
            COUNTROWS(
                FILTER( 'Data1' , 
                    'Data1'[Actual Finish] > _Current_Date ) ) + 1 , 
                ALLEXCEPT( 'Data1' , 
                    'Data1'[Sub Discipline] ) ) ) )

VAR _vTable_2 = 
ADDCOLUMNS(
    _vTable_1 ,
    "@Max_Actual_Finish_Date" , 
    IF( 
        SUMX( 
            _vTable_1 , 
            [@Ranking_Based_On_Activity_ID] ) = 
        SUMX( 
            _vTable_1 , 
            [@Ranking_Based_On_Actual_Finish_Date] ) , 
        CALCULATE( MAX( 'Data1'[Actual Finish] ) , 
            ALLEXCEPT( 'Data1' , 'Data1'[Discipline] ) ) ) )

VAR _Results = 
MAXX(
    _vTable_2 , 
        [@Max_Actual_Finish_Date] )

RETURN
_Results

In order to cross-verify that “Discipline” measure is working as expected, I introduced the same data but this time with the “Actual Finish Date” for all the “Sub-Discipline” activities so that “Discipline” shows the expected resultant date. Below is the screenshot of that result provided for the reference -

I’m also attaching the working of the PBIX file for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Important Note: In the PBIX file, the original data which you’ve provided is termed as “Data1” whereas the new data which I’ve created for testing purposes is termed as “Data2”.

EDIT:- Edited the “Actual Finish Date” measures

Thanks and Warm Regards,
Harsh

Actual Finish Date - Harsh.pbix (61.1 KB)