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

    Bronze Contributor
    Posted Sep 05, 2019 04:20 AM
    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
    ------------------------------
    Conference-PBI_200x200


  • 2.  RE: Sum a Duration

    Top Contributor
    Posted Sep 05, 2019 05:56 AM
      |   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
    ------------------------------

    Conference-PBI_200x200


  • 3.  RE: Sum a Duration

    Bronze Contributor
    Posted Sep 05, 2019 06:26 AM

    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

     




    Conference-PBI_200x200


  • 4.  RE: Sum a Duration

    Bronze Contributor
    Posted Sep 07, 2019 07:19 AM
    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
    ------------------------------

    Conference-PBI_200x200


  • 5.  RE: Sum a Duration

    Top Contributor
    Posted Sep 10, 2019 11:54 PM
    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
    ------------------------------

    Conference-PBI_200x200


  • 6.  RE: Sum a Duration

    Bronze Contributor
    Posted Sep 11, 2019 03:56 AM
    Edited by Andreas Ratz Sep 11, 2019 04:33 AM
    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
    ------------------------------

    Conference-PBI_200x200


  • 7.  RE: Sum a Duration

    Top Contributor
    Posted Sep 11, 2019 05:17 AM
    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
    ------------------------------

    Conference-PBI_200x200


  • 8.  RE: Sum a Duration

    Bronze Contributor
    Posted Sep 11, 2019 11:08 AM
    ROUNDDOWN works fine

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

    Conference-PBI_200x200