dax time difference help

[drad2211]

Hi

I have a somewhat similar question. I have a table that has the following columns (ID, Time, Action) and I want to add a new column (I believe by making a DAX equation) that can show the time difference (in mins) for specific ID and the 'In' 'Out' Action. I would like the time difference to be shown only on the 'Out' row. The data file is titled 'Data' and the table looks like: 

ID                                       Time                             Action

1                            08:00:00 05/05/2015                   In

1                            11:00:00 05/05/2015                  Out

2                            12:00:00 05/05/2015                   In

1                            13:00:00 05/05/2015                   In

1                            15:00:00 05/05/2015                  Out

2                            17:00:00 05/05/2015                  Out

.                                           .                                      .

.                                           .                                      .

And I would like the table to look like:

ID                                       Time                             Action                     Time Diff

1                            08:00:00 05/05/2015                   In

1                            11:00:00 05/05/2015                  Out                           180

2                            12:00:00 05/05/2015                   In

1                            13:00:00 05/05/2015                   In

1                            15:00:00 05/05/2015                  Out                           120

2                            17:00:00 05/05/2015                  Out                           420

.                                           .                                      .

.                                           .                                      .

Any advice would be helpful. 

Thank you

[v-diye-msft · Accepted solution]

Hi @drad2211 

1. Add the index column in power query

2. Add the measure below:

Measure = var a = CALCULATE(MAX('Table (2)'[Time]),FILTER(ALL('Table (2)'),MAX('Table (2)'[Action])="Out"&&[Index]<MAX('Table (2)'[Index])),VALUES('Table (2)'[ID]))
Return
DATEDIFF(a,MAX('Table (2)'[Time]),MINUTE)

 

[amitchandak]

Refer to this example, if can also use lookupvalue

https://community.powerbi.com/t5/Desktop/DAX-LOOKUPVALUE-with-MAX/td-p/696076

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[parry2k]

@drad2211 how did you get to 420?

[drad2211]

sorry 300

[MartynRamsden]

Hi @drad2211 

Add a calculated column using the following DAX expression:

Time Diff = 
VAR RowID = Table1[ID]
VAR RowOutDate = Table1[Date]
VAR InDate = 
CALCULATE ( 
    MAX ( Table1[Date] ),
    FILTER ( 
        ALL ( Table1 ),
        Table1[ID] = RowID
            && Table1[Action] = "In"
            && Table1[Date] <= RowOutDate
    )
)
VAR Result =
IF ( 
    Table1[Action] = "Out",
    (RowOutDate - InDate) * 1440
)
RETURN Result

You'll need to replace any reference to 'Table1' with your actual table name.

Best regards,

Martyn

If I answered your question, please help others by accepting it as a solution.