Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
jalaomar
Helper IV
Helper IV

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%

jalaomar_0-1668689187198.png

 

BR

J

 

1 ACCEPTED SOLUTION

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:

vkalyjmsft_0-1669371478847.png

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.

View solution in original post

8 REPLIES 8
v-yanjiang-msft
Community Support
Community Support

Hi @jalaomar ,

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

vkalyjmsft_0-1668753651870.png

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:

vkalyjmsft_1-1668753845040.png

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.

vkalyjmsft_2-1668754893993.png

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.

vkalyjmsft_3-1668754995922.png

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.

 

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?

jalaomar_0-1669111018068.png

 

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:

vkalyjmsft_0-1669371478847.png

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.

Hi @v-yanjiang-msft is it possible to share your pbix? I can't stil not make the measure work for me.

 

Thanks!

Hi @jalaomar ,

Please refer to the attached sample.

Best Regards,
Community Support Team _ kalyj

 

Hi @v-yanjiang-msft , thanks for looking into this.

 

but so wierd, have tried several times and i am getting the following error when trying your measure. Do you know why?

 

jalaomar_0-1669994929690.png

 

Hi @jalaomar ,

Maybe your fact data is more complicated than my sample, you can seperately return each part in the formula to check which part can't get the correct result than dig it.

Best Regards,
Community Support Team _ kalyj

Hi @v-yanjiang-msft 

 

Will do so!

 

just another question in the meantime. Have created 2 measures and want to create another measure with some IF statment. so if Project Group column is "PACKAGING" then display  measure1 and if Project Group column is "Processing System" & "Processing Key Comp." then display measure 2.

 

Thinks it's simple solution but can't get my head around it.

 

jalaomar_0-1670243295906.png

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.