Substract time columns from diff datasets

[LearnerMM]

Dear Mentors,

I need to substarct column "EST Start" from "EST" ...basically trying to calculate delay.

"EST" indicates schedule - Source is dataset1

"EST Start"  indicate actual start time - source is dataset2

Both dataset1 and dataset2 are connected using manage relationship.

Could you guide me how can I substract below columns from each other - to get delay in minutes? 

[v-karpurapud]

Hi @LearnerMM 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-karpurapud]

Hi @LearnerMM 

We are following up to see if your query has been resolved. Should you have identified a solution, we kindly request you to share it with the community to assist others facing similar issues.

If our response was helpful, please mark it as the accepted solution and provide kudos, as this helps the broader community.

Thank You!

[Anonymous]

Hi @LearnerMM,

Any update on this? Can you please share some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[rohit1991]

Hi ,
To calculate the delay in minutes between the "EST Start" (actual start time from dataset2) and "EST" (scheduled time from dataset1), you need to ensure that both datasets are properly related using a common key in Manage Relationships. Once the relationship is set, you can create a new calculated column or measure in Power BI using DAX. The difference between these two time columns can be computed by converting them into a datetime format and subtracting them. Since the result of subtracting time values is in days, multiplying by 1440 (minutes per day) converts it into minutes. Below is the DAX formula to achieve this:
1)  DAX Query for Delay Calculation (in Minutes)
DelayMinutes =
DATEDIFF(SELECTEDVALUE(Dataset1[EST]), SELECTEDVALUE(Dataset2[EST Start]), MINUTE)

2) Alternatively, if working with a calculated column:
DelayMinutes =
(Dataset2[EST Start] - Dataset1[EST]) * 1440

This will return the delay in minutes, where positive values indicate a late start, and negative values indicate an early start. If needed, you can also use ABS(DelayMinutes) to get the absolute delay.

[LearnerMM]

Hey Thanks @rohit1991 @Deku @danextian  ...... actually problem is - It dose not pull out column name from other datasource.

We can substract 2 columns from same data source - but if you try to select another datasource columns , Power BI is not giving me option to select from other datasource. 

Can  Iconnect with anyone of you ? 

Thanks

Mangesh M

[danextian]

Can you please show your diagram/relationship view? If there's a direct  one-to-many relationship between the two tables, you should be able to use RELATED on the many side. The RELATED function cannot be used on the "one" side of a one-to-many relationship because there could be multiple matching rows in the related table, and RELATED does not perform aggregation.

[Deku]

Datediff( datum[EST start], datum[EST], minutes)

[LearnerMM]

Hey Thanks @Deku  ...... actually problem is - It dose not pull out column name from other datasource.

We can substract 2 columns from same data source - but if you try to select another datasource columns , Power BI is not giving me option to select from other datasource. 

Can  Iconnect with anyone of you ? 

Thanks

Mangesh M

[danextian]

Hi @LearnerMM 

By "manage relationship" you mean there is a direct relationship between the two tables or is there a dimension table in between? Which one's on the many side? Which one's on the one side? Some functions will work depending on which side of the relationship the table is? Please show your diagram.

[LearnerMM]

Yes,  there is a direct relationship between the two tables

[LearnerMM]

Hey Thanks @danextian  ...... actually problem is - It dose not pull out column name from other datasource.

We can substract 2 columns from same data source - but if you try to select another datasource columns , Power BI is not giving me option to select from other datasource. 

Can  Iconnect with anyone of you ? 

Thanks

Mangesh M

[tharunkumarRTK]

@LearnerMM 

Please provide the details of the relationship you created. For example if [EST] column is in Table1 and [EST Start] column is in Table2 and if I assume you created 
One to Many relationship between these two 

Table1 on one side and Table2 on the many side then you can follow this DAX measure syntax 

Delay = SUMX(Table2, Datediff(Related(Table1[EST]),  Table2[EST Start], Minute  ) )

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!