Forum Discussion
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 and then table y is up-to-date values of that same table as the day progresses.
Both the tables have a column= planned_duration.
I have renamed the column in table x to initial_duration.
I want to now calculate the difference between planned and initial duration to calculate the variance but they are both in different tables.
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.
I dont want to join the tables since thats complicated and I want them both to be different tables not one.
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.
9 Replies
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/1447523I 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}Sorry, if you cannot find time to create meaningful sample data then I cannot find time to assist you.
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.
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.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
Hi maxkhan
Whether your problem has been solved, and if not, whether you can provide some sample data？
Best Regards!
Yolo Zhu
