Forum Discussion

maxkhan's avatar
maxkhan
Frequent Visitor
1 year ago

Calculating Time

Hi, So I have got 2 tables.   For instance, table x and table y. Table x is basically a snapshot or the same table as table y, however table x is a snapshot taken at the start of a day at 05.30am ...
Need Help
Show and Tell
Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi,

Thanks for lbendlin 's concern about the problem, and i want to offer some more information for user to refer to.

hello maxkhan , based on your descriotion, i create the following sample data

Table X

Table Y:

Then in tabke y, can create a calculated column.

Column =
VAR _initial =
    MAXX ( FILTER ( x, [ID] = EARLIER ( Y[ID] ) ), [Initial_duration] )
RETURN
    ABS ( [planned_duration] - _initial )

 

 

Output

 

 

Best Regards!

Yolo Zhu

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

 

  • maxkhan's avatar
    maxkhan
    Frequent Visitor
    1 year ago

     
    Hi, as you can see it's giving incorrect calculation. It should give Planned_Duration - Initial_Duration.
    Variance is providing with Actual_Planned.

    Please note that planned_duration is in table y and Initial_Duration is in table x and they have primary key as common.

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi maxkhan 

      Thank you for your reply. Is the primary key between your two tables a many-to-many relationship? Can you create some sample data? I don't need you to provide your original data. As long as you create some examples, I can provide you with solutions. The sample data I create may be different from yours.

       

      Best Regards!

      Yolo Zhu

      • maxkhan's avatar
        maxkhan
        Frequent Visitor
        1 year ago

        No, it is one to many in both of them. Are you happy to do a webex call to help me sort this out?