Visually compare scheduled hours and completed hours

[Aknas]

Hi All,

I am trying to visually compare scheduled hours with completed hours to identify incomplete shifts. Despite my attempts, I have been unable to achieve the desired result. I have attached a sample workbook that includes both tables and the intended outcome for reference.

Here is a brief overview of what I have been attempting:

1. I have created a table matrix where the "Scheduled Start Shift Date and Time" column is compared to the "Completed Start Shift Date and Time" column.
2. The objective is to fill the cell with green if there is a match between the two columns, indicating a completed shift. Conversely, the cell should be filled with red if there is no match, implying an incomplete shift.

However, my current implementation has not yielded the expected results. Therefore, I am seeking your expertise to help me resolve this issue. I would greatly appreciate any guidance or insights you can provide.

Please find the attached sample workbook, which includes the necessary tables and the desired outcome, for your reference. Your assistance in this matter would be invaluable to me.

Thank you for your time and consideration

Dii

		Scheduled table
Category	Worker ID	start_date_time	end_date_time
A	1	2022-10-24 10:00:00	2022-10-25 18:00:00
A	1	2022-10-26 10:00:00	2022-10-26 18:00:00
B	2	2022-10-17 14:00:00	2022-10-17 22:00:00
B	2	2022-10-05 17:00:00	2022-10-06 00:00:00
C	3	2022-10-06 17:00:00	2022-10-06 00:00:00

	Completed table
Worker ID	start_date_time	end_date_time	duration	Category
1	2022-10-24 10:00:00	2022-10-25 18:00:00	8	A
2	2022-10-17 14:00:00	2022-10-17 22:00:00	0	B
2	2022-10-05 17:00:00	2022-10-06 00:00:00	7	B

[v-yanjiang-msft · Accepted solution]

Hi @Aknas ,

According to your description, here's my solution.

Column in the matrix is of text format in my sample.

Create two measures.

Value =
IF (
    MAX ( 'Table'[Column1] )
        IN SELECTCOLUMNS (
            'Scheduled',
            "date", FORMAT ( 'Scheduled'[start_date_time], "DD-MMM" )
        )
            && MAX ( 'Table'[Column1] )
                IN SELECTCOLUMNS (
                    'Completed',
                    "DATE", FORMAT ( 'Completed'[start_date_time], "DD-MMM" )
                ),
    " ",
    IF (
        MAX ( 'Table'[Column1] )
            IN SELECTCOLUMNS (
                'Scheduled',
                "date", FORMAT ( 'Scheduled'[start_date_time], "DD-MMM" )
            ),
        " "
    )
)

Color =
IF (
    MAX ( 'Table'[Column1] )
        IN SELECTCOLUMNS (
            'Scheduled',
            "date", FORMAT ( 'Scheduled'[start_date_time], "DD-MMM" )
        )
            && MAX ( 'Table'[Column1] )
                IN SELECTCOLUMNS (
                    'Completed',
                    "DATE", FORMAT ( 'Completed'[start_date_time], "DD-MMM" )
                ),
    "GREEN",
    IF (
        MAX ( 'Table'[Column1] )
            IN SELECTCOLUMNS (
                'Scheduled',
                "date", FORMAT ( 'Scheduled'[start_date_time], "DD-MMM" )
            ),
        "ORANGE"
    )
)

Put the measure Value in matrix values and in the matrix visualizations formatting>Cell elements, turn on the Backgroud color option and click "fx".

Select the measure color in the below dialog.

Get the correct result:

I attach my sample below for your reference.

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

Best regards,

Community Support Team_yanjiang

[v-yanjiang-msft · Accepted solution]

Hi @Aknas ,

According to your description, here's my solution.

Column in the matrix is of text format in my sample.

Create two measures.

Value =
IF (
    MAX ( 'Table'[Column1] )
        IN SELECTCOLUMNS (
            'Scheduled',
            "date", FORMAT ( 'Scheduled'[start_date_time], "DD-MMM" )
        )
            && MAX ( 'Table'[Column1] )
                IN SELECTCOLUMNS (
                    'Completed',
                    "DATE", FORMAT ( 'Completed'[start_date_time], "DD-MMM" )
                ),
    " ",
    IF (
        MAX ( 'Table'[Column1] )
            IN SELECTCOLUMNS (
                'Scheduled',
                "date", FORMAT ( 'Scheduled'[start_date_time], "DD-MMM" )
            ),
        " "
    )
)

Color =
IF (
    MAX ( 'Table'[Column1] )
        IN SELECTCOLUMNS (
            'Scheduled',
            "date", FORMAT ( 'Scheduled'[start_date_time], "DD-MMM" )
        )
            && MAX ( 'Table'[Column1] )
                IN SELECTCOLUMNS (
                    'Completed',
                    "DATE", FORMAT ( 'Completed'[start_date_time], "DD-MMM" )
                ),
    "GREEN",
    IF (
        MAX ( 'Table'[Column1] )
            IN SELECTCOLUMNS (
                'Scheduled',
                "date", FORMAT ( 'Scheduled'[start_date_time], "DD-MMM" )
            ),
        "ORANGE"
    )
)

Put the measure Value in matrix values and in the matrix visualizations formatting>Cell elements, turn on the Backgroud color option and click "fx".

Select the measure color in the below dialog.

Get the correct result:

I attach my sample below for your reference.

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

Best regards,

Community Support Team_yanjiang

[Aknas · Accepted solution]

Thanks Yan.

The solution works Perfect!!

Thanks again

[Aknas]

Corrected the screenshot ( desired result)