Time calculation with DAX or conditional column

[Pamdelorbe]

Hi everyone!

I would like to obtain CTA time based on the carrier's subtraction time frame.

For example, if the carrier is Jet Blue, subtract 75 minutes or (1 h 15 mm) to the specified time.

11:28 AM - 1:15 (or 75 minutes) = 10:13 AM.

Please see above the time frame for each carrier. Not sure is the solution should be a calculated column or metric.

Thanks in advance for your help!! 

[andhiii079845 · Accepted solution]

It try it with a calculated table (not in Power Query Editor), it works with time and datetime:

Column = if('Table'[Carrier]="JET BLUE",'Table'[time]-'Table'[time frame])

[andhiii079845]

Please post some example data with the table function, no screenshot please

[Pamdelorbe]

Hi, 

Let me show you:

1. I try to create a conditional column. For example. if Carrier = Jet Blue, Time Frame = 75 (minutes). 

Then created a custom colum to subtract time_frame from real time (Hora ATD):

Here is the Error: 

I think might be the data type I selected for time_frame. 

Please let me know iyour comments. 

Thanks, 

2. Then I tried to subtract the time frame from Time column. But the result is the time itself and not the subtracted calculated time. 

Then 

[andhiii079845]

Hora ATD is a real time or and you want to subtract hours from it? This is not possible. You need something like this:
https://community.powerbi.com/t5/Desktop/How-to-add-hours-to-DateTime-data/td-p/104443

[Pamdelorbe]

Yes. Hora ATD is a real time and depending the carrier I would like to subtract 90, 75 or 30 minutes in order to obtain a new colum with the subtracted exact time.  

Thanks, 

[andhiii079845 · Accepted solution]

It try it with a calculated table (not in Power Query Editor), it works with time and datetime:

Column = if('Table'[Carrier]="JET BLUE",'Table'[time]-'Table'[time frame])

[Pamdelorbe]

Hi,

One more question:

What format time frame should be? as "time" for example 1:15 AM (1 hour and 15 minutes) or  75 minutes (duration). 

This is to the formula be able to do the calculation between the two columns and not show error. 

[andhiii079845 · Accepted solution]

In my example 
Column = if('Table'[Carrier]="JET BLUE",'Table'[time]-'Table'[time frame])
both time.
I do not understand your question 🙂 

[Pamdelorbe]

Hi, 

I think it worked!

Just one more question: How can add multiple carriers to the same formula and also.. 

If else statement?

For example...

Column = if('Table'[Carrier]="JET BLUE" and IBERIA and CONDOR,'Table'[time]-'Table'[time frame])...

Thanks!

[andhiii079845]

Yes like this: 

Column = if('Table'[Carrier] in {"JET BLUE, "Test"},'Table'[time]-'Table'[time frame])

[andhiii079845]

Hora ATD has the datatype "datetime", right? Or "time"?

[Pamdelorbe]

HI, 

Only time (see below). 

I have a doubt about this formula:

[DateTime] + #duration(0,10,0,0))

Since time frame relies on a conditional statement (carrier). How can I specify time frame based on the carrier?

Thanks, 

[andhiii079845]

It depands if you can do this calculation in the underlaying data. Because it is fix to the columns. You can use calculated columns. If you need to modify it while filtering "on the fly" than use DAX.

[Pamdelorbe]

Would you please show me both ways? 

I try to create a calculated column but only got ERROR.

Thanks,