Forum Discussion
dax time difference help
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
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)
5 Replies
sorry 300
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 ResultYou'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.
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-trend/ba-p/882970
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p/881739
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601Hi 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)
