Forum Discussion

RonaldvdH's avatar
RonaldvdH
Post Patron
2 years ago

Calculate time between

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...
Need Help
RonaldvdH's avatar
RonaldvdH
Post Patron
2 years ago

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's avatar
Fowmy
Super User
2 years ago

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's avatar
    RonaldvdH
    Post Patron
    2 years ago

    Fowmy 

    So my ServiceWindow table should be adjusted to this ?

     

    DayBeginEnd
    Monday08:00:0020:00:00
    Tuesday08:00:0020:00:00
    Wednesday08:00:0020:00:00
    Thursday08:00:0020:00:00
    Friday08:00:0020:00:00
    Saturday08:00:0012: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's avatar
      RonaldvdH
      Post Patron
      2 years ago

      Fowmy first of all best wishes for 2024 😉

       

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

      • Fowmy's avatar
        Fowmy
        Super User
        2 years ago

        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.