Re: Calculate the difference between 2 columns in 2 separate tables

Hi toni14

Open edit Queries,

in Table1, select "Total active hours", "Total inactive hours","Total hours logged on" columns and unpivot them.

then merge columns "User" and "Attribute" with separator "space".

The same done in the Table2

Close&&apply

create a relationship between Table1 and Table2 based on merged column

create measures in Table1 and Table2

in Table1

seconds1 =
VAR h =
    SEARCH ( "h", MAX ( Table1[Value] ), 1, 0 )
VAR m1 =
    LEFT ( MAX ( Table1[Value] ), h - 1 )
VAR m =
    SEARCH ( "m", MAX ( Table1[Value] ), 1, 0 )
VAR m2 =
    MID ( MAX ( Table1[Value] ), h + 2, m - ( h + 2 ) )
VAR s =
    SEARCH ( "s", MAX ( Table1[Value] ), 1, 0 )
VAR m3 =
    MID ( MAX ( Table1[Value] ), m + 2, s - ( m + 2 ) )
RETURN
    m1 * 60
        * 60
        + m2 * 60
        + m3

In Table2

seconds2 =
VAR h =
    SEARCH ( "h", MAX ( Table2[Value] ), 1, 0 )
VAR m1 =
    LEFT ( MAX ( Table2[Value] ), h - 1 )
VAR m =
    SEARCH ( "m", MAX ( Table2[Value] ), 1, 0 )
VAR m2 =
    MID ( MAX ( Table2[Value] ), h + 2, m - ( h + 2 ) )
VAR s =
    SEARCH ( "s", MAX ( Table2[Value] ), 1, 0 )
VAR m3 =
    MID ( MAX ( Table2[Value] ), m + 2, s - ( m + 2 ) )
RETURN
    m1 * 60
        * 60
        + m2 * 60
        + m3

Then create measures in Table1

sub = [seconds1]-[seconds2]

h_m_s =
INT ( [sub] / 3600 )
    & "h "
    & INT ( ( [sub] - INT ( [sub] / 3600 ) * 3600 ) / 60 )
    & "m "
    & MOD ( [sub], 60 )
    & "s"

Best reagrds

Maggie