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

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

Herbert,

 

Thank you so much for your help.

 

But unfortunately DATEDIFF  gives an error "In DATEDIFF function, the start date cannot be greater than the end date"

because some hours on "DateTimeFrom" are greater than the "FirstDayEndTime" as you can see on the picture

 

I tried to fix this, but I couldn't do it.

 

Thank you!

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

dcs136

 

Please update the DAX formula of FirstDaySecDiff column as below and have a try again.

 

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

Best Regards,

Herbert

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

    Herbert,

     

    Thanks again for your help and sorry for bothering you again,

     

    The last problem was solved, but I still have a problem with the MidDaysSecDiff column.

    As you can see on the screen below, It gives an erro saying that DAX can't compare date values with True/False values.

     

    • 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
        )

         

  • nadirS's avatar
    nadirS
    Icon for Helper I rankHelper I
    5 years ago

    is there a way i can do a reversal on the same thing that you explained above - I have a date table and i am able to calculate working days.(0s for weekends and 1's for Weekdays). I need to add 5 days to my start date and and pick the appropriate working date from the date table so that it gives me an "Expected Completion Date" that takes account of weekends.