Calculating Time

Question

Hi, So I have got 2 tables.&nbsp;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 and then table y is up-to-date values of that same table as the day progresses.&nbsp;Both the tables have a column= planned_duration.&nbsp;I have renamed the column in table x to initial_duration.&nbsp;I want to now calculate the difference between planned and initial duration to calculate the variance but they are both in different tables.&nbsp;Whats the best way to calculate this? Calculated column? It doesnt allow me to subtract two diff tables column together or measures gives incorrect data. Not sure.&nbsp;I dont want to join the tables since thats complicated and I want them both to be different tables not one.&nbsp;Tablex(intial_duration) - Tabley(planned_duration) = variance {please tell the best way to do this}Also both are in one decimal format such as 2.5 = 2 and a half hours.

lbendlin · Answer

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

maxkhan · Answer

I can't provide a sample data since this is really confidential.

However, it's like.

Planned_Duration = 4.5 in table y
Initial_Duration = 3.5 in table x

Expected outcome should be = Planned_Duration in table y - Initial_Duration in table x

So it would be {Variance = 1.0}

lbendlin · Answer

Sorry, if you cannot find time to create meaningful sample data then I cannot find time to assist you.

anonymous · Answer

Hi,
Thanks for&nbsp;lbendlin&nbsp;'s concern about the problem, and i want to offer some more information for user to refer to.
hello&nbsp;maxkhan&nbsp;, 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 )

&nbsp;
&nbsp;
Output

&nbsp;
&nbsp;
Best Regards!
Yolo Zhu
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly.
&nbsp;

maxkhan · Answer

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.

Forum Discussion

Calculating Time

9 Replies

Related content

calculating time

Calculate Time

Calculating day/time difference

Wrong Time Calculation

Calculating Days late with specific target times