Difference between tables

[Sjmiller]

Two table difference

I am trying to calculate the difference between two tables that are only related to each other on date. The ultimate goal is to find the times where the actual time used in the servicelog was different than what they scheduled in the Appt table. So I will conditionally format those times.

This possible?

Difference2 =

VAR ApptValue =

SUMX(FILTER('Appts', 'Appts'[ApptDate] = MAX('LOG'[SRVDATE])), [Hours])

VAR SValue =

SUMX('LOG', [Hours])

RETURN

IF(ApptValue <> SValue, 1, 0)

The measure above works when it is a simple card visual, but when I add this measure to the matrix it does not work because of the filter context,
so I tried this measure, to account for that and its still marking everyone as 1.

Difference =

VAR ApptValue =

SUMX(

FILTER(

ALL('Appts'),

'Appts'[ApptDate] = MAX('LOG'[SRVDATE]) &&

'Appts'[custid] = SELECTEDVALUE('Appts'[custid]) &&

'Appts'[StaffName] = SELECTEDVALUE('Appts'[StaffName])

),

[Hours]

)

VAR SValue =

SUMX(

FILTER(

ALL('log'),

'Log'[cust_id] = SELECTEDVALUE('log'[cust_id]) &&

'Log'[StaffName] = SELECTEDVALUE('LOG'[StaffName])

),

[Hours]

)

RETURN

IF(ApptValue <> SValue, 1, 0)

[swikritee_p · Accepted solution]

@Sjmiller , check if this code can help

Difference = 
VAR CurrentDate = SELECTEDVALUE('LOG'[SRVDATE])
VAR CurrentCustID = SELECTEDVALUE('LOG'[cust_id])
VAR CurrentStaffName = SELECTEDVALUE('LOG'[StaffName])

VAR ApptValue = 
    CALCULATE(
        SUM('Appts'[Hours]),
        FILTER(
            'Appts',
            'Appts'[ApptDate] = CurrentDate &&
            'Appts'[custid] = CurrentCustID &&
            'Appts'[StaffName] = CurrentStaffName
        )
    )

VAR SValue = 
    CALCULATE(
        SUM('LOG'[Hours]),
        FILTER(
            'LOG',
            'LOG'[SRVDATE] = CurrentDate &&
            'LOG'[cust_id] = CurrentCustID &&
            'LOG'[StaffName] = CurrentStaffName
        )
    )

RETURN IF(ApptValue <> SValue, 1, 0)

[Sjmiller]

This worked thanks!