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
Rhndy's avatar
Rhndy
Frequent Visitor
8 years ago

i think i found out what's wrong here:

- The middayssecdiff has the servicehours hard-coded (3600 * 10). My window is 9,5 hours, by changing it to 3600 * 9.5 that was allright and got a small deviation fixed.

 

- The formula for FirstDaySecDiff produces 0 when the ticket hasn't been resolved the same day. That's wrong.

The formula in your file:

IF (
'SLA'[FirstDayEndTime] >= 'SLA'[DateTimeFrom]
&& 'SLA'[FirstDayEndTime] <= 'SLA'[DateTimeTo] && RELATED('Calendar'[WorkDay]) = TRUE ();
DATEDIFF ( 'SLA'[DateTimeFrom]; 'SLA'[FirstDayEndTime]; SECOND );

 

Fails on the RELATED('Calendar'[WorkDay]) = TRUE ();

When turning that off, it works. But it doesn't when a ticket got created in the weekend. As we speak i'm checking to fix that.

Anonymous's avatar
Anonymous
Not applicable
7 years ago

I changed a view things to work with regionalization a little bit easier.

 

In the time calculations dont use & "18:00:00" because then PowerBI has to interpret a text to a date which is depending on the regional settings. Instead just add the time as datetime object like so: + TIME(18,0,0).

When you do it like that you will always receive an datetime object and its easy (and propably faster) to interpret for PowerBI.

 

I also used a public holiday calendar and imported it to another table and created a * to Many relationship between the calendar.date and the hollidays.date.  My Calculated Column in Calendar now checks if it was a holiday:

Holliday = CALCULATE( MIN(Hollidays[HollidayName]), ALLEXCEPT('Calendar','Calendar'[Date]))

and then you can check if ( isblank(holliday)) which will give you if the date was a holiday or not. I added to the workday check if the day was a holliday or not.