Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Sum a Duration

  • 1.  Sum a Duration

    Posted 17 days ago
    Hi

    I have a table with working hours and minutes (come and go). I want to summarize it. Format HH:MM does not work. Any ideas?

    DurationDayDec is the duration in days as decimal -> 1.5 days

    Duration =
    FORMAT(
    SUM(Zeiterfassung[DurationDaysDec]),
    "HH:mm"
    )
     --> 11:55 is not korrekt -> should be 35:55

    Best Andreas

    ------------------------------
    Andreas Ratz
    CEO
    Widnau
    +41798055000
    ------------------------------


  • 2.  RE: Sum a Duration

    Top Contributor
    Posted 17 days ago
      |   view attached

    A little help thru this url and little bit of fine tune. I think it should work for you.

    Duration = 
    VAR TotalSeconds=SUMX(Zeiterfassung,HOUR(Zeiterfassung[DurationDaySec])*3600+MINUTE(Zeiterfassung[DurationDaySec])*60+SECOND(Zeiterfassung[DurationDaySec]))
    VAR Days =TRUNC(TotalSeconds/3600/24)
    VAR Hors = TRUNC((TotalSeconds-Days*3600*24)/3600)
    VAR Mins =TRUNC(MOD(TotalSeconds,3600)/60)
    VAR Secs = MOD(TotalSeconds,60)
    return IF((Hors + (Days*24))<10,"0"&(Hors + (Days*24)),(Hors + (Days*24)))&":"&IF(Mins<10,"0"&Mins,Mins)&":"&IF(Secs<10,"0"&Secs,Secs)


    ------------------------------
    Farhan Ahmed
    Senior Business Intelligence Consultant
    karachi
    3452523688
    ------------------------------



  • 3.  RE: Sum a Duration

    Posted 17 days ago

    Hi Ahmed

     

    Works!!! Thx a lot

     

    Best

    Andreas

     

    Mit freundlichen Grüssen

    Andreas Ratz

    RBS Logo - ohne text -  Facebook

    Ratz Business Solutions

    Unterlettenstrasse 10

    CH-9443 Widnau

     

    Tel. +41  79 805 5000

    Mail andreas.ratz@ratz-bs.ch

    Web www.ratz-bs.ch

     






  • 4.  RE: Sum a Duration

    Posted 15 days ago
    May I have your help again?

    In the mean time I have a table with the plannend hours per month

    YearMonth Arbeitstage Urlaubstage Hours Plan Dec Days Plan Dec
    201807 22 2.08333 39.83334 1.6597225
    201808 22 2.08333 39.83334 1.6597225
    201809 21 2.08333 37.83334 1.576389167

    I have copied the Code and modified a little bit to calculate the measure for the planned hours

    Hours Plan =
    VAR TotalSeconds=
    SUMX(Sollarbeitszeit,
    HOUR(Sollarbeitszeit[Days Plan Dec])*3600+
    MINUTE(Sollarbeitszeit[Days Plan Dec])*60+
    SECOND(Sollarbeitszeit[Days Plan Dec]))
    VAR Days =
    TRUNC(TotalSeconds/3600/24)
    VAR Hours =
    TRUNC((TotalSeconds-Days*3600*24)/3600)
    VAR Mins =
    TRUNC(MOD(TotalSeconds,3600)/60)
    VAR Secs =
    MOD(TotalSeconds,60)
    return
    IF((Hours + (Days*24))<10,"0"&(Hours + (Days*24)),(Hours + (Days*24)))&":"&IF(Mins<10,"0"&Mins,Mins)&":"&IF(Secs<10,"0"&Secs,Secs)


    The result in the report is

    Any Idea what I have made wrong?

    Best
    Andreas


    ------------------------------
    Andreas Ratz
    CEO
    Widnau
    +41798055000
    ------------------------------



  • 5.  RE: Sum a Duration

    Top Contributor
    Posted 11 days ago
    HI @Andreas Ratz​,

    It seems that hour function not capturing days information. So adding # of days in formula will result correct.

    Hours Plan = 
    VAR TotalSeconds=
    SUMX(Sollarbeitszeit, 24*MOD(Sollarbeitszeit[Days Plan Dec],1) *3600 + // Adding Day since hour function not returning days number
    HOUR(Sollarbeitszeit[Days Plan Dec])*3600+
    MINUTE(Sollarbeitszeit[Days Plan Dec])*60+
    SECOND(Sollarbeitszeit[Days Plan Dec]))
    VAR Days =
    TRUNC(TotalSeconds/3600/24)
    VAR Hours =
    TRUNC((TotalSeconds-Days*3600*24)/3600)
    VAR Mins =
    TRUNC(MOD(TotalSeconds,3600)/60)
    VAR Secs =
    ROUND(MOD(TotalSeconds,60),2)
    return
    IF((Hours + (Days*24))<10,"0"&(Hours + (Days*24)),(Hours + (Days*24)))&":"&IF(Mins<10,"0"&Mins,Mins)&":"&IF(Secs<10,"0"&Secs,Secs)



    ------------------------------
    Farhan Ahmed
    Senior Business Intelligence Consultant
    karachi
    3452523688
    ------------------------------



  • 6.  RE: Sum a Duration

    Posted 11 days ago
    Edited by Andreas Ratz 11 days ago
    Hi @Farhan Ahmed

    Thx again for your help. We are very close now. But still some hours are missing. The result now is ​

    The source shows

    Replaced your function to capture the days by

    24 * ROUNDDOWN(Sollarbeitszeit[Days Plan Dec],0) * 3600

    Is this correct?

    Best
    Andreas


    ------------------------------
    Andreas Ratz
    CEO
    Widnau
    +41798055000
    ------------------------------



  • 7.  RE: Sum a Duration

    Top Contributor
    Posted 11 days ago
    If ROUNDDOWN not giving you correct results then Try use FLOOR instead of ROUNDDOWN

    24*FLOOR(Sollarbeitszeit[Days Plan Dec],1)


    ------------------------------
    Farhan Ahmed
    Senior Business Intelligence Consultant
    karachi
    3452523688
    ------------------------------



  • 8.  RE: Sum a Duration

    Posted 11 days ago
    ROUNDDOWN works fine

    ------------------------------
    Andreas
    ------------------------------