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? 

[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.

Passionate about leveraging data analytics to drive strategic decision-making and foster business growth.

Connect with me on LinkedIn: Rohit Kumar

[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!