Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
@moosepng , 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())
Thanks Mate!
@moosepng , 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())
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
82 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |