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

10 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

Advocate 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

Microsoft 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

Forum Discussion

Calculate Date and Time difference considering the weekends and workhours