Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have 2 columns, Activation Time (Time IN) + End Time (Time OUT). The goal is to calculate the time difference between Activation Time - End Time.
CURRENT PROBLEM: The current output will take the literal difference between the 2 columns and not take into account if the time is crossing over from one day to the next.
EX: In line 1 = Activation Time (10:08:53 PM) - End Time (2:04:40 PM) = -8hrs [Which is incorrect]
ANTICIPATED OUTPUT: The goal is to create calculated column which will take into account the date if the time crosses over from one day to the next.
EX: Activation Time (10:08:53 PM) - End Time (2:04:40 PM) = 16hrs (15hrs 55mins)
Activation Date | Activation Time | EndDate | EndTime | TimeKey - ActiveTime | ActiveEndDATEDIFF | ActiveEndTIMEDIFF | TimeKey - EndTime |
Wednesday, December 21, 2016 | 10:08:53 PM | Thursday, December 22, 2016 | 2:04:40 PM | 2208 | 1 | -8 | 1404 |
Wednesday, December 21, 2016 | 10:08:53 PM | Thursday, December 22, 2016 | 4:17:13 PM | 2208 | 1 | -6 | 1617 |
Wednesday, December 21, 2016 | 10:08:53 PM | Thursday, December 22, 2016 | 4:23:42 PM | 2208 | 1 | -6 | 1623 |
Below is my current DAX (which I know is not correct). Any advice will be greatly appreciated.
Solved! Go to Solution.
The current problem is because there is no date part in the time stamp you are using to calculate the difference.
Looks like your data source contains date and time in separate columns, so you need to create a custom column by combining date and time columns, for both Activation and End times.
Then you can use datediff function to calculate the time difference between the two time stamps.
Time Difference = DATEDIFF ( Activation_Date_Time, End_Date_Time, Hour)
The current problem is because there is no date part in the time stamp you are using to calculate the difference.
Looks like your data source contains date and time in separate columns, so you need to create a custom column by combining date and time columns, for both Activation and End times.
Then you can use datediff function to calculate the time difference between the two time stamps.
Time Difference = DATEDIFF ( Activation_Date_Time, End_Date_Time, Hour)
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |