Time between 2 dates that includes results that are less than 1 day.

[dbkeller12]

Hello,

I am hoping someone to can help me as I am completely new to Power BI and seem to be stuck.

I am using the below formula to calculate the number of Workdays between 2 dates (EnteredDate, ClosedDate).

Workdays Calculated = 
    VAR numDays = DATEDIFF([EnteredDate], [ClosedDate].[Date], DAY) + 1

    
    VAR weekNumStart = WEEKNUM([EnteredDate])
    VAR weekNumEnd = WEEKNUM([ClosedDate])

    VAR numWeekends = IF(
        weekNumStart <= weekNumEnd, //weeks in same year
        weekNumEnd - weekNumStart, //the difference is the number of weekends
        weekNumEnd - weekNumStart + 52 //if different years, adds the number of weeks in one year
    )
RETURN
    numDays - (numWeekends * 2) //calculates the number of days (*2 to remove Saturday and Sunday for each weekend)

This works well with one exception.  I need to get more granular than this formula is giving me and I am not sure if this is possible.  For example, in Excel when I calculate the difference between the follow dates I get a number below 1 (.00010189 to be exact), see below.

Parameters:

Entered Date = 1/2/18 8:55:39AM

Closed Date = 1/2/18 8:55:47AM

Excel calculation: EnteredDate - ClosedDate = Time between 2 dates

                            1/2/18 8:55:39AM  -  1/2/18 8:55:47AM = .00010189

Is it possible to make adjustments to the formula that I am using to return .00010189 for this example and not what is currently returns, which is 1.0?

I appreciate the help in advance.

Thanks!!

[AlB]

Hi @dbkeller12

 If you want to change the behavior of the code only in that particular case and want to keep the changes to your current code to a minimum you could just modify slightly the last line:

Workdays Calculated = 
    VAR numDays = DATEDIFF([EnteredDate], [ClosedDate].[Date], DAY) + 1
    
    VAR weekNumStart = WEEKNUM([EnteredDate])
    VAR weekNumEnd = WEEKNUM([ClosedDate])

    VAR numWeekends = IF(
        weekNumStart <= weekNumEnd, //weeks in same year
        weekNumEnd - weekNumStart, //the difference is the number of weekends
        weekNumEnd - weekNumStart + 52 //if different years, adds the number of weeks in one year
    )
RETURN
   IF( numDays = 1,
      [ClosedDate]-[EnteredDate],     
      numDays - (numWeekends * 2)
   ) 

Two additional things:

1. I believe there's no real need to use .[Date] in 

DATEDIFF([EnteredDate], [ClosedDate].[Date], DAY)

since the DATEDIFF with DAY as interval will already ignore the time

2. I understand [EnteredDate] and  [ClosedDate] are columns, correct? If so, it's a best practice to use the fully qualified names, i.e. including the table name: TableName[ColumnName]. This is to easily tell apart columns from measures, which are referred to without table name:  [Measure]     

[dbkeller12]

AlB thanks for the reply. 

I think this may work however I do need to for everything, not just the numDays that equal to 1.  So correct me if I am wrong, but to apply this to anything that is >=1 I would just have to do the below, correct?

  IF( numDays >= 1, [ClosedDate]-[EnteredDate],    
      numDays - (numWeekends * 2)
   )

I did this and it appeared to work correct, but I figured that I would ask anyway.

I also removed the .[Date] that you suggested and that worked fine, thank you for correcting me on that.

And yes, EnteredDate and ClosedDate are columns in my data set.

Let me know if my adjustment to >=1 will result in getting all

Thanks again for the assistance.