Forum Discussion
Calculate Column with Time Since Entry
I have a records table, with each row having a unique date time.
The table contains information about people entering and exiting a state, as well as their status.
How do I calculate a column that records the difference in time between entry and exit (days) at the time of exit?
Table:
| DateTime | ID | Status | Entry | Exit |
| 25/10/2020 14:25 | 1001 | A | ||
| 29/10/2020 14:25 | 1001 | B | 1 | 0 |
| 1/12/2020 15:30 | 1002 | 1 | 0 | |
| 5/02/2021 12:00 | 1001 | B | 0 | 1 |
| 15/04/2021 21:45 | 1002 | 0 | 1 | |
| 20/04/2021 21:45 | 1003 | A | 0 | 0 |
My expected result is:
| DateTime | ID | Time Since Entry |
| 5/02/2021 12:00 | 1001 | 99 |
| 15/04/2021 21:45 | 1003 | 135 |
I want to be able to say - In April the average time since entry was 135 days.
I have a measure that calculates the Time Since Entry:
Time Since Entry =
AVERAGEX(
VALUES('Table'[ID]),
CALCULATE(
DATEDIFF(
MINX(FILTER('Table', 'Table'[Entry] =1), [DateTime]),
MAXX(FILTER('Table', 'Table'[Exit] =1), [DateTime]),
DAY)
)
)
However, the problem is that when I Filter this measure in my report by Month it excludes the datetime of entry when entry is not in April.
I therefore want to calculate a column, and tried the following DAX:
Time Since Entry =
IF(
'Table'[Exit] = 1,
AVERAGEX(
VALUES('Table'[ID]),
CALCULATE(
DATEDIFF(
MINX(FILTER('Table', 'Table'[Entry] =1), [DateTime]),
MAXX(FILTER('Table', 'Table'[Exit] =1), [DateTime]),
DAY)
)
),
9999997
)
However this gives me:
| DateTime | ID | Status_A | Entry | Exit | Time Since Entry |
| 25/10/2020 14:25 | 1001 | A | 99999997 | ||
| 29/10/2020 14:25 | 1001 | 1 | 0 | 99999997 | |
| 1/12/2020 15:30 | 1002 | 1 | 0 | 99999997 | |
| 5/02/2021 12:00 | 1001 | 0 | 1 | ||
| 15/04/2021 21:45 | 1002 | 0 | 1 |
First post:
https://community.powerbi.com/t5/Desktop/DATEDIFF-between-DateTime-Values-by-ID/m-p/1838306#M711595
Similar Issues:
https://community.powerbi.com/t5/Desktop/Calculating-time-difference/m-p/1528385#M628335
https://community.powerbi.com/t5/DAX-Commands-and-Tips/DATEDIFF-with-filter/m-p/769728
https://community.powerbi.com/t5/Desktop/using-datediff-with-filters/m-p/528938
Anonymous , Create a column like this and take an average of that
if([Exit] =1, datediff(maxx(filter('Table', [ID] =earlier([ID]) && [Entry] =1 && [DateTime] <= earlier([DateTime])),[DateTime]),[DateTime], day), blank())
2 Replies
- amitchandakSuper User
Anonymous , Create a column like this and take an average of that
if([Exit] =1, datediff(maxx(filter('Table', [ID] =earlier([ID]) && [Entry] =1 && [DateTime] <= earlier([DateTime])),[DateTime]),[DateTime], day), blank())
- AnonymousNot applicable
Thanks Mate!