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
    10 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

v-haibl-msft's avatar
v-haibl-msft
Icon for Microsoft Employee rankMicrosoft Employee
10 years ago

dcs136

 

It seems there are extra expression in your formula screenshot as below. How about the result if you remove it?

If problem persits, could you please give a complete screenshot for this MidDaysSecDiff formula?

 

 

Best Regards,

Herbert

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

Hi Herbert!

 

I removed this line but the problem still remains!

 

I compared the both codes and they are the same, as you can see below

 

MidDaysSecDiff =
IF (
    FORMAT ( [DateTimeFrom]; "Short Date" )
        <> FORMAT ( [DateTimeTo]; "Short Date" );
    3600 * 10
        * (
            CALCULATE (
                DISTINCTCOUNT ( 'Calendar'[Date] );
                FILTER (
                    'Calendar';
                    'Calendar'[Date] > [FirstDayEndTime]
                        && 'Calendar'[Date] < [LastDayStartTime]
                        && 'Calendar'[WorkDay] = TRUE ()
                )
            )
                - 1
        );
    0
)

 

  • v-haibl-msft's avatar
    v-haibl-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    10 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

     

    • RicardoAlfa's avatar
      RicardoAlfa
      Frequent Visitor
      8 years ago

      I have been working with this code, but is possible agree a new column to limit the TimeFrom date, and it can considering to the final result in hours

      For example:

       

      TimeFromTimeTo
      01/01/2018 07:0001/01/2018 10:00

       

      My Result is 3 hours, but it should be 2 hours, considering the business hours in the day 08:00 am to 18:00 pm, May be I must agree a column like the LastDayStartTime, but using the First Star Time.

       

      Some Insight?

       

      Thanks!

      • hmedinaa's avatar
        hmedinaa
        Frequent Visitor
        8 years ago

         I have the same issue, wondering if we are missing a FirstDayStartTime, if the starting time is before 8am  (7:00:00AM) and finish time is 10:00:00 AM is giving me has result the regular time diff, = 3hrs

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

      Hebert!

       

      Thanks again!

       

      It was exactly what you said.

       

      The column Workday was duplicate.

       

      Now it's working perfect!!

       

       

    • dcs136's avatar
      dcs136
      Icon for Advocate I rankAdvocate I
      9 years ago

      Hello Herbert,

       

      I need a little help here again.

       

      I tried to solve it but I couldn't find an solution.

       

      As you can see on the picture bellow, the calculation between two dates is correclty just when the date is different. But when the difference is just between hours the result is the DateTimFrom column - FirstDayEndTime Column.

       

      For example:

      03/01/2016 10:50:42  = 7,155 - but the result should be 2,046