Forum Discussion

dcs136's avatar
dcs136
Icon for Advocate I rankAdvocate I
10 years ago
Solved

Calculate Date and Time difference considering the weekends and workhours

  Hi everybody!   I'm still learning how to use Power BI, I search for this everywhere but I didn't found an answer! Here is the thing,   I need to calculate the difference between dates and tim...
Need Help
  • v-haibl-msft's avatar
    v-haibl-msft
    9 years ago

    dcs136

     

    The only possibility I can think of is that there is something wrong with the Calendar'[WorkDay] formula. Could you please check this column formula is like below? The data type of this column should be “True/False”.

    If it is actually same as mine, could you please upload your .pbix file to OneDrive and share it with me? In that case I can take a look at your .pbix file and try to solve the problem.

     

     

    Best Regards,

    Herbert

     

  • v-haibl-msft's avatar
    v-haibl-msft
    9 years ago

    dcs136

     

    How about the result if we update the “FirstDaySecDiff” measure as below?

     

    FirstDaySecDiff = 
IF (
    Table1[FirstDayEndTime] >= Table1[DateTimeFrom]
        && Table1[FirstDayEndTime] <= Table1[DateTimeTo],
    DATEDIFF ( Table1[DateTimeFrom], Table1[FirstDayEndTime], SECOND ),
    IF (
        Table1[FirstDayEndTime] >= Table1[DateTimeFrom]
            && Table1[FirstDayEndTime] > Table1[DateTimeTo],
        DATEDIFF ( Table1[DateTimeFrom], Table1[DateTimeTo], SECOND ),
        0
    )
)

     

    Best Regards,

    Herbert

itshudak's avatar
itshudak
Icon for Helper I rankHelper I
7 years ago

Sam,

 

I am actually running into an issue on some of the output.

FirstStartDateTime: 10/7/2017 1:20:00 PM

CreateDate: 10/10/2017 8:39:18 AM

 

An error occurred in the ‘’ query. Expression.Error: The 'increment' argument is out of range.
Details:
1.00:00:00

 

I'm not sure what is happening here. I kept everything the same, and also changed the startTime, but am getting the same error. Any ideas?

Anonymous's avatar
Anonymous
Not applicable
7 years ago

itshudak looks like you passed the arguments in the wrong order (doing that reproduced the exact error for me).

I've updated my code above to give a helpful error message in that case

  • itshudak's avatar
    itshudak
    Icon for Helper I rankHelper I
    7 years ago

    Reversing the inputs actually gives me the same error, but 10 times worse. The output should be positive, correct? Regardless, I don't know what this error is actually saying, but my data looks correct, so I don't think it's a formatting issue. I'm just confused as to what needs to be fixed to get it to work properly. I have 100k rows that work, but these 100 or so errors have got me for a loop.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      itshudak can you put together a reproducible example? Check this out for pointers

      Easiest way would be to put together a CSV with 2 columns (start and end time) and 5-10 rows, made up from your problem rows. Import this into Power BI and make sure you still get the error

       

      I'll then check it out and see what I can do

      • itshudak's avatar
        itshudak
        Icon for Helper I rankHelper I
        7 years ago

        Nevermind, consider this egg on my face. It was actually an issue with the stop time being before the start time and I was just getting mixed up. Thank you for your help with this, I appreciate it.