Calculate time between

[RonaldvdH]

Hey guys,

I have a problem and i'm hoping for your help.

I use this formula to calculate the hours between 2 dates and it works like a charm, the variable is that it calculates the hours between 2 dates based on a service window (monday till saturday 08.00 till 20.00)

The table servicewindow is a basic table with:

Day	ServiceWindow
Start	08:00:00
Eind	20:00:00

The problem is that the servicewindow for saterday has changed to 08.00 till 12.00 

Maybe I need an extra VAR for a ServiceWindowWeekend ? or a formula that checks the StartDate (or EndDate) and if it's a Saturday it used different Start/Eind times

There is a relationship between this Incidents table and the Date table based on StartDate but I need an additional condition in Var EersteDagVerstrekenTijd and VAR LaatsteDagVerstrekenTijd.

Both VAR's calculate based on a 12 hour servicewindow (monday through saturday) but:

Examples for what i'm expecting:

- if a ticket starts on a Saturday > 12.00 the VAR EersteDagVerstrekenTijd should be 0

- if a ticket ends on a Saturday > 12.00 the VAR LaatsteDagVerstrekenTijd should be 4

- If a ticket starts on Friday 16.00 and ends on Monday on 12.00 the formula now calculates that there are 20 hours between both  dates (4 hours friday, 12 hours saturday and 4 hours monday) but the hours on saturday should be 4 (ServiceWindow Saturday: 08.00-12.00) so a total of 12 hrs

- If a ticket starts on Thursday 19.00 and ends on Saturday on 14.00 the formula now calculates that there are 19 hours between both dates (1 hour Thursday, 12 hours Friday and 6 hours Saturday) but the hours on saturday should be 4 (ServiceWindow Saturday: 08.00-12.00) so a total of 17 hrs

- If a ticket starts on Saturday 16.00 and ends on Monday on 09.00 the formula now calculates that there are 5 hours between both dates (4 hours Saturday and 1 hour on Monday) but the hours on saturday should be 0 (ServiceWindow Saturday: 08.00-12.00) so a total of 1 hrs

Can anyone help me out here ?

Doorlooptijd Ticket in Uren = 
VAR ServiceWindowStart = Calculate(SELECTEDVALUE(ServiceWindow[ServiceWindow]),ServiceWindow[Dag]="Start")
VAR ServiceWindowEind = Calculate(SELECTEDVALUE(ServiceWindow[ServiceWindow]),ServiceWindow[Dag]="Eind")
VAR ServiceWindowPerDag = VALUE(ServiceWindowEind-ServiceWindowStart)*24
VAR DezeStartTijd = Incidenten[StartTijd]
VAR DezeEindTijd = Incidenten[EindTijd]
VAR StartDatum =Incidenten[StartDate]
VAR EindDatum = Incidenten[EndDate]
VAR EersteDagVerstrekenTijd = Switch(TRUE(),
                               Related('Date'[ServiceWindow])=0,0,                      
                               DezeStartTijd>=ServiceWindowEind,0,                      
                                
                               StartDatum = EindDatum && DezeEindTijd < ServiceWindowEind,Round((DezeEindTijd-DezeStartTijd)*24,3),   
                               ROUND((ServiceWindowEind-DezeStartTijd)*24,3))          
VAR LaatsteDagVerstrekenTijd = Switch(TRUE(),  
                               LOOKUPVALUE('Date'[ServiceWindow],'Date'[Date],EindDatum)=0,0,
                               DezeEindTijd<=ServiceWindowStart,0,                      
                               DezeEindTijd>=ServiceWindowEind,ServiceWindowPerDag,     
                               StartDatum = EindDatum ,0,                               
                               ROUND((DezeEindTijd-ServiceWindowStart)*24,3))           
VAR VolledigeWerkdagen = Calculate (sum('Date'[ServiceWindow]),DATESBETWEEN('Date'[Date],StartDatum+1,EindDatum-1)) 
VAR TotaalAantalUren = EersteDagVerstrekenTijd+VolledigeWerkdagen*ServiceWindowPerDag+LaatsteDagVerstrekenTijd
Return TotaalAantalUren

[RonaldvdH]

Any ideas besides me sharing the file ?

I was hoping that a adjustment to the formula to take in account the saturday was the way to go

[Fowmy]

@RonaldvdH 

I asked for a dummy file to streamline our communication and expedite the solution process, minimizing back-and-forth exchanges. This enables me to clearly understand the model you're working on, facilitating a more direct solution.

In any case, the recommended approach is to generate a table with seven rows, each corresponding to a day (1, 2, 3, and so forth), along with start and end times. By iterating through this table, you can determine the time range necessary for your calculations.
 

[RonaldvdH]

@Fowmy 

So my ServiceWindow table should be adjusted to this ?

Day	Begin	End
Monday	08:00:00	20:00:00
Tuesday	08:00:00	20:00:00
Wednesday	08:00:00	20:00:00
Thursday	08:00:00	20:00:00
Friday	08:00:00	20:00:00
Saturday	08:00:00	12:00:00

But what would the formula be then ?

Doorlooptijd Ticket in Uren = 
VAR ServiceWindowStart = Calculate(SELECTEDVALUE(ServiceWindow[ServiceWindow]),ServiceWindow[Dag]="Start")
VAR ServiceWindowEind = Calculate(SELECTEDVALUE(ServiceWindow[ServiceWindow]),ServiceWindow[Dag]="Eind")
VAR ServiceWindowPerDag = VALUE(ServiceWindowEind-ServiceWindowStart)*24
VAR DezeStartTijd = Incidenten[StartTijd]
VAR DezeEindTijd = Incidenten[EindTijd]
VAR StartDatum =Incidenten[StartDate]
VAR EindDatum = Incidenten[EndDate]
VAR EersteDagVerstrekenTijd = Switch(TRUE(),
                               Related('Date'[ServiceWindow])=0,0,                      
                               DezeStartTijd>=ServiceWindowEind,0,                      
                                
                               StartDatum = EindDatum && DezeEindTijd < ServiceWindowEind,Round((DezeEindTijd-DezeStartTijd)*24,3),   
                               ROUND((ServiceWindowEind-DezeStartTijd)*24,3))          
VAR LaatsteDagVerstrekenTijd = Switch(TRUE(),  
                               LOOKUPVALUE('Date'[ServiceWindow],'Date'[Date],EindDatum)=0,0,
                               DezeEindTijd<=ServiceWindowStart,0,                      
                               DezeEindTijd>=ServiceWindowEind,ServiceWindowPerDag,     
                               StartDatum = EindDatum ,0,                               
                               ROUND((DezeEindTijd-ServiceWindowStart)*24,3))           
VAR VolledigeWerkdagen = Calculate (sum('Date'[ServiceWindow]),DATESBETWEEN('Date'[Date],StartDatum+1,EindDatum-1)) 
VAR TotaalAantalUren = EersteDagVerstrekenTijd+VolledigeWerkdagen*ServiceWindowPerDag+LaatsteDagVerstrekenTijd
Return TotaalAantalUren

[RonaldvdH]

@Fowmy first of all best wishes for 2024 😉

I was still hoping you could help me out with my problem, any ideas ?

[Fowmy]

@RonaldvdH 

Sharing a dummy Power BI file representing your scenario would be beneficial. You can save the Power BI file on Google Drive or any other cloud storage platform and provide the link here. Kindly ensure that permission is granted to open the file.

[Fowmy]

@RonaldvdH 

Sharing a dummy Power BI file representing your scenario would be beneficial. You can save the Power BI file on Google Drive or any other cloud storage platform and provide the link here. Kindly ensure that permission is granted to open the file.