Calculating Time

[maxkhan]

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.

[Anonymous]

Hi @maxkhan 

Whether your problem has been solved, and if not, whether you can provide some sample data？

Best Regards!

Yolo Zhu

[Anonymous]

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]

 
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]

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]

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

[Anonymous]

Hi @maxkhan 

If one direction is x table, and the 'many direction' is  y table, the dax i offered should work, can you provide the code you entered? and we can't call , it is better that you can provide some sample data.

Best Regards!

Yolo Zhu

[lbendlin]

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-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[maxkhan]

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]

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