Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ktt777
Helper V
Helper V

calculate time and round up/down

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 IDFrom DateFrom TimeTo DateTo TimeStartEndDayscalculated days
211Friday, 27 May 20221:09:00 pmFriday, 27 May 20226:15:00 pm27/5/2022 13:0927/5/2022 18:150.21250.5
229 12:00:00 am 12:00:00 am30/12/1899 12:00:00 am30/12/1899 12:00:00 am#VALUE!0
171  Wednesday, 11 May 20228:49:00 am 11/5/2022 8:4944692.36736 
156Monday, 9 May 20222:20:00 pmMonday, 9 May 20222:45:00 pm9/5/2022 14:209/5/2022 14:450.0173611110.5
154Monday, 9 May 20222:06:00 pmMonday, 9 May 20222:20:00 pm9/5/2022 14:069/5/2022 14:200.0097222220.5
366Wednesday, 8 June 20228:00:00 amWednesday, 8 June 202212:00:00 pm8/6/2022 8:008/6/2022 12:000.1666666670.5
174Wednesday, 11 May 20221:00:00 pmThursday, 12 May 20228:00:00 am11/5/2022 13:0012/5/2022 8:000.7916666671
185Saturday, 14 May 20228:51:00 amSunday, 15 May 20226:52:00 pm14/5/2022 8:5115/5/2022 18:521.4173611112
117Thursday, 28 April 20228:00:00 amSaturday, 30 April 20226:00:00 pm28/4/2022 8:0030/4/2022 18:002.4166666673
71Friday, 1 April 20228:00:00 amSaturday, 9 April 20226:00:00 pm1/4/2022 8:009/4/2022 18:008.4166666679
163Wednesday, 2 March 202212:11:00 pmTuesday, 22 March 20226:10:00 pm2/3/2022 12:1122/3/2022 18:1020.2493055621
158Tuesday, 10 May 20228:00:00 amTuesday, 31 May 20226:00:00 pm10/5/2022 8:0031/5/2022 18:0021.4166666722
223Thursday, 9 June 20228:00:00 amThursday, 30 June 20226:00:00 pm9/6/2022 8:0030/6/2022 18:0021.4166666722

ktt777_0-1657349392118.png

 

1 ACCEPTED SOLUTION
Kishore_Kadhir
Resolver II
Resolver II

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(

TRUE(),
DATEDIFF('Date'[Start Date], 'Date'[End Date], MINUTE) / 60 < 6, 0.5,
DATEDIFF('Date'[Start Date], 'Date'[End Date], MINUTE) / 60 >= 6 && DATEDIFF('Date'[Start Date], 'Date'[End Date], MINUTE) / 60 <=24, 1,
DATEDIFF('Date'[Start Date], 'Date'[End Date], MINUTE) / 60 = 25, 1.5,
DATEDIFF('Date'[Start Date], 'Date'[End Date], MINUTE) / 60 = 31, 2
). You can modify this DAX to meet your requirments.
 
Regards,
Kishore

View solution in original post

1 REPLY 1
Kishore_Kadhir
Resolver II
Resolver II

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(

TRUE(),
DATEDIFF('Date'[Start Date], 'Date'[End Date], MINUTE) / 60 < 6, 0.5,
DATEDIFF('Date'[Start Date], 'Date'[End Date], MINUTE) / 60 >= 6 && DATEDIFF('Date'[Start Date], 'Date'[End Date], MINUTE) / 60 <=24, 1,
DATEDIFF('Date'[Start Date], 'Date'[End Date], MINUTE) / 60 = 25, 1.5,
DATEDIFF('Date'[Start Date], 'Date'[End Date], MINUTE) / 60 = 31, 2
). You can modify this DAX to meet your requirments.
 
Regards,
Kishore

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.