Forum Discussion

Sjmiller's avatar
Sjmiller
Regular Visitor
2 years ago
Solved

Difference between tables

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)

Need Help
  • swikritee_p's avatar
    swikritee_p
    2 years ago

    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)

2 Replies

  • swikritee_p's avatar
    swikritee_p
    Resolver II
    2 years ago

    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)