Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
i used Dax formula to create new column Start= [From Date]+[From Time] , End =[To Date]+ [To Time]
how can i modify them so if any column From Date, From Time, To Date, To Time is blank then the value in Start, End column is blank as well?
i want to calculate the duration of the trip by taking End column - Start column ? if the number of hours is less than 6 hrs, it is 0.5 days. From 6hrs to 24 hours it is 1 day. If the trip is 25 hours, it is 1.5 days . if the trip is 31 hours , it is 2 days.
the column Calculated days is my expected resutl. How can i write Dax formula for that ?
Journey ID | From Date | From Time | To Date | To Time | Start | End | Days | calculated days |
211 | Friday, 27 May 2022 | 1:09:00 pm | Friday, 27 May 2022 | 6:15:00 pm | 27/5/2022 13:09 | 27/5/2022 18:15 | 0.2125 | 0.5 |
229 | 12:00:00 am | 12:00:00 am | 30/12/1899 12:00:00 am | 30/12/1899 12:00:00 am | #VALUE! | 0 | ||
171 | Wednesday, 11 May 2022 | 8:49:00 am | 11/5/2022 8:49 | 44692.36736 | ||||
156 | Monday, 9 May 2022 | 2:20:00 pm | Monday, 9 May 2022 | 2:45:00 pm | 9/5/2022 14:20 | 9/5/2022 14:45 | 0.017361111 | 0.5 |
154 | Monday, 9 May 2022 | 2:06:00 pm | Monday, 9 May 2022 | 2:20:00 pm | 9/5/2022 14:06 | 9/5/2022 14:20 | 0.009722222 | 0.5 |
366 | Wednesday, 8 June 2022 | 8:00:00 am | Wednesday, 8 June 2022 | 12:00:00 pm | 8/6/2022 8:00 | 8/6/2022 12:00 | 0.166666667 | 0.5 |
174 | Wednesday, 11 May 2022 | 1:00:00 pm | Thursday, 12 May 2022 | 8:00:00 am | 11/5/2022 13:00 | 12/5/2022 8:00 | 0.791666667 | 1 |
185 | Saturday, 14 May 2022 | 8:51:00 am | Sunday, 15 May 2022 | 6:52:00 pm | 14/5/2022 8:51 | 15/5/2022 18:52 | 1.417361111 | 2 |
117 | Thursday, 28 April 2022 | 8:00:00 am | Saturday, 30 April 2022 | 6:00:00 pm | 28/4/2022 8:00 | 30/4/2022 18:00 | 2.416666667 | 3 |
71 | Friday, 1 April 2022 | 8:00:00 am | Saturday, 9 April 2022 | 6:00:00 pm | 1/4/2022 8:00 | 9/4/2022 18:00 | 8.416666667 | 9 |
163 | Wednesday, 2 March 2022 | 12:11:00 pm | Tuesday, 22 March 2022 | 6:10:00 pm | 2/3/2022 12:11 | 22/3/2022 18:10 | 20.24930556 | 21 |
158 | Tuesday, 10 May 2022 | 8:00:00 am | Tuesday, 31 May 2022 | 6:00:00 pm | 10/5/2022 8:00 | 31/5/2022 18:00 | 21.41666667 | 22 |
223 | Thursday, 9 June 2022 | 8:00:00 am | Thursday, 30 June 2022 | 6:00:00 pm | 9/6/2022 8:00 | 30/6/2022 18:00 | 21.41666667 | 22 |
Solved! Go to Solution.
1) You can create a column and try the following:
Start = IF(From Date = BLANK() || From Time = BLANK(), BLANK(), [From Date]+[From Time])
End = IF(To Date = BLANK() || To Time = BLANK(), BLANK(), [To Date]+[To Time])
2) Days = SWITCH(
1) You can create a column and try the following:
Start = IF(From Date = BLANK() || From Time = BLANK(), BLANK(), [From Date]+[From Time])
End = IF(To Date = BLANK() || To Time = BLANK(), BLANK(), [To Date]+[To Time])
2) Days = SWITCH(
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |