Forum Discussion

jalaomar's avatar
jalaomar
Helper IV
3 years ago
Solved

measure schedule performance

Hi,

 

I have projects with their Tollgate dates (ITG0, ITG0.1 etc.) which is visulized in a matrix table. I have calculated the datediff between Contract Baseline and ActualsSchedule.

 

Now I would like to add another measure which will indicate how well the plan is going in %. 

 

so for example for ITG0.1

Take datediff between ITG0.1 Actual with ITG0 Actual and divide with datediff ITG0.1 contract and ITG0 contract.

 

continuing like this for ITG1.

 

Take datediff between ITG1 Actual with ITG0 Actual and divide with datediff ITG1 contract and ITG0 contract.

 

anyone know how to create a measure with the % Performance?

 

just and exaple case below 

 

Example 2ITG 0ITG 0.1ITG 1ITG 2
Planned Date2022-01-012023-02-012024-03-012025-04-01
Actual Date2022-01-012023-02-102024-02-252025-04-25
Days Variance0-95-24
% Performance100%102%99%102%

 

BR

J

 

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    Hi jalaomar ,

    Sorry for late back, I modify the formula:

    % Performance =
DIVIDE (
    DATEDIFF (
        MAXX (
            FILTER (
                ALL ( 'Table' ),
                'Table'[Tollgate] = "ITG 0"
                    && 'Table'[Baseline] = "Actual Date"
            ),
            'Table'[Date]
        ),
        MAXX (
            FILTER (
                ALL ( 'Table' ),
                'Table'[Baseline] = "Actual Date"
                    && 'Table'[Tollgate] = MAX ( 'Table'[Tollgate] )
            ),
            'Table'[Date]
        ),
        DAY
    ),
    DATEDIFF (
        MAXX (
            FILTER (
                ALL ( 'Table' ),
                'Table'[Tollgate] = "ITG 0"
                    && 'Table'[Baseline] = "Actual Date"
            ),
            'Table'[Date]
        ),
        MAXX (
            FILTER (
                ALL ( 'Table' ),
                'Table'[Baseline] = "Planned Date"
                    && 'Table'[Tollgate] = MAX ( 'Table'[Tollgate] )
            ),
            'Table'[Date]
        ),
        DAY
    )
)

    Get the correct result:

    Best Regards,
    Community Support Team _ kalyj

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

8 Replies

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Community Support
    3 years ago

    Hi jalaomar ,

    According to your description, based on the snapshot you provided, I create a sample.

    Here's my solution.

    1.Create a calculated column.

    Rank =
RANKX ( 'Table', 'Table'[Tollgate],, ASC, DENSE )

    2.Create two measures.

    Days Variance =
DATEDIFF (
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Baseline] = "Actual Date"
                && 'Table'[Tollgate] = MAX ( 'Table'[Tollgate] )
        ),
        'Table'[Date]
    ),
    MAXX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Baseline] = "Planned Date"
                && 'Table'[Tollgate] = MAX ( 'Table'[Tollgate] )
        ),
        'Table'[Date]
    ),
    DAY
)

    % Performance =
DIVIDE (
    DATEDIFF (
        MAXX (
            FILTER (
                ALL ( 'Table' ),
                'Table'[Rank]
                    = MAX ( 'Table'[Rank] ) - 1
                    && 'Table'[Baseline] = "Actual Date"
            ),
            'Table'[Date]
        ),
        MAXX (
            FILTER (
                ALL ( 'Table' ),
                'Table'[Baseline] = "Actual Date"
                    && 'Table'[Tollgate] = MAX ( 'Table'[Tollgate] )
            ),
            'Table'[Date]
        ),
        DAY
    ),
    DATEDIFF (
        MAXX (
            FILTER (
                ALL ( 'Table' ),
                'Table'[Rank]
                    = MAX ( 'Table'[Rank] ) - 1
                    && 'Table'[Baseline] = "Actual Date"
            ),
            'Table'[Date]
        ),
        MAXX (
            FILTER (
                ALL ( 'Table' ),
                'Table'[Baseline] = "Planned Date"
                    && 'Table'[Tollgate] = MAX ( 'Table'[Tollgate] )
            ),
            'Table'[Date]
        ),
        DAY
    )
)

    Get the result:

    Based on the data structure, the result is not displaying in the same struture with your expected, but the value is correct. If you want to get the expected structure, you should create a new table.

    Sort the Row column by Sort column. Don't make relationship between the two tables. Then create a measure.

    Measure =
SWITCH (
    MAX ( 'Row'[Row] ),
    "Actual Date",
        CONVERT (
            MAXX ( FILTER ( 'Table', 'Table'[Baseline] = "Actual Date" ), 'Table'[Date] ),
            STRING
        ),
    "Planned Date",
        CONVERT (
            MAXX ( FILTER ( 'Table', 'Table'[Baseline] = "Planned Date" ), 'Table'[Date] ),
            STRING
        ),
    "Days Variance", [Days Variance],
    "% Performance", FORMAT ( [% Performance], "#%" )
)

    Get the expected result.

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ kalyj

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

     

    • jalaomar's avatar
      jalaomar
      Helper IV
      3 years ago

      Hello, Thanks for supporting with this.

      but just found that the %Performance is not calculating as I would like to.

       

      below you can view the expected calculation and outcome 

      so for each tollgate calculate the datediff = Actual ITG0 - Contract ITG0

      ITG0 % Performance = Actual ITG0 / Contract ITG0

      ITG0.1 % Performance = Actual ITG0.1-Actual ITG0 / Contract ITG0.1 - Contract ITG0

      ITG1 % Performance = Actual ITG1-Actual ITG0 / Contract ITG1 - Contract ITG0

       

      After each tollgate the % performance is always calculated against ITG0.

       

      Hope you can follow what I mean. Is there a way to tweak the measure you have created a little bit?

       

      • v-yanjiang-msft's avatar
        v-yanjiang-msft
        Community Support
        3 years ago

        Hi jalaomar ,

        Sorry for late back, I modify the formula:

        % Performance =
DIVIDE (
    DATEDIFF (
        MAXX (
            FILTER (
                ALL ( 'Table' ),
                'Table'[Tollgate] = "ITG 0"
                    && 'Table'[Baseline] = "Actual Date"
            ),
            'Table'[Date]
        ),
        MAXX (
            FILTER (
                ALL ( 'Table' ),
                'Table'[Baseline] = "Actual Date"
                    && 'Table'[Tollgate] = MAX ( 'Table'[Tollgate] )
            ),
            'Table'[Date]
        ),
        DAY
    ),
    DATEDIFF (
        MAXX (
            FILTER (
                ALL ( 'Table' ),
                'Table'[Tollgate] = "ITG 0"
                    && 'Table'[Baseline] = "Actual Date"
            ),
            'Table'[Date]
        ),
        MAXX (
            FILTER (
                ALL ( 'Table' ),
                'Table'[Baseline] = "Planned Date"
                    && 'Table'[Tollgate] = MAX ( 'Table'[Tollgate] )
            ),
            'Table'[Date]
        ),
        DAY
    )
)

        Get the correct result:

        Best Regards,
        Community Support Team _ kalyj

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