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

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi!

Thank you for your help!!

I think there is a mistake in your solution. 

 

Formula for FirstDaySecDiff is only correct if DateTimeFrom's date is different from DateTimeTo's date

 

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

 

 Otherwise the right formula is as follows:

 

FirstDaySecDiff = 
IF (
FORMAT (Table1[DateTimeFrom], “Short Time”) = FORMAT (Table1[DateTimeTo], “Short Time”),
DATEDIFF ( Table1[DateTimeFrom], Table1[DateTimeTo], SECOND ),
DATEDIFF ( Table1[DateTimeFrom], Table1[FirstDayEndTime], SECOND )
)

 

I tried with this new formula and now i get right calculation results.

 

Best regards,

Andrés