Forum Discussion

Yenn's avatar
Yenn
Frequent Visitor
3 years ago

Calculate the different hours between two tables

Hi @everyone,

I am trying to calculate the different hours between two tables but it's not working, what should I do in this case?

 

Table 2: 

Table 1: 

 When I typed the formula below, it shown: 

Hour comparison =
var _Table1 =SELECTEDVALUE(fHour[Hours])
var _Table2 =SELECTEDVALUE('fResults[Hour])
return
_Table2 - _Table1
 

 

Need Help
Tips and Tricks
Tutorial Requests

3 Replies

  • AmiraBedh's avatar
    AmiraBedh
    Icon for Super User rankSuper User
    3 years ago

    Are you considering the ID in your calculation ?

    Logically, I can calculate the diffrence in hours between the two tables for the same ID.

    In that case you need to join the two tables on the IDs so that you can perform calculations on the corresponding rows.You can then calculate the difference in hours between the two tables for the corresponding IDs.

    Create Relationships Between Tablesi f you haven't already, you might want to create a relationship between the tables using the common ID field.

    and then :

    Difference_in_Hours =
SUMX(
    Table1, // Assuming you want to iterate through Table1
    VAR RelatedRow = RELATEDTABLE(Table2) // Fetch related row from Table2
    VAR Hours1 = Table1[hours] // Get hours from Table1
    VAR Hours2 = SUMX(RelatedRow, Table2[hours]) // Get hours from related row in Table2
    RETURN Hours1 - Hours2 // Return the difference
)

     

     

  • Yenn's avatar
    Yenn
    Frequent Visitor
    3 years ago

    I already created the relationship "Many-to-Many" between two tables and I tried to using this formula but it didnt work. I want to describe in details two reports as the following:

    + Table 1 ( Hour report): each ID displayed the total hours for which operated everyday. Some IDs will repeat or they are added or removed depend on the business situation.

    + Table 2 ( Results): each ID displayed the total hours that customer spent everyday.

    For example: On 19/01/2019, ID: BJ-01-01, Table 1 (Hour) = 21.37, Table 2 (Results) = 8.15

    And the result I want to show: