Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

calculate difference between two dates using DAX

Hi All,

 

i'm facing one chellange i have two dates with time (start date , end date) need to calculate how long it tooks for the activity except weekends. Ex. 22 july  - 26 july  ( here middle 23,24 july as fall in  weekend hence we need to exclude) how to get as expected result attached here.

 

 

Thanks,
MS

Need Help
  • TomMartens's avatar
    TomMartens
    3 years ago

    Hey Anonymous ,


    important: the relationship between the Calendar table and the Branch data table must be deleted.

    I tackle these kinds of challenges by turning datetime columns into seconds, then doing the math needed based on the seconds, and finally formatting the result into some kind of readable format. This article might provide additional information: A duration is not the same as datetime! - Mincing Data - Gain Insight from Data (minceddata.info)

    this measure

    durtion without weekend and holidays = 
var durationInSeconds =
    SUMX(
        'Branch data'
        , var datetimeStart = CALCULATE( MAX( 'Branch data'[start date] ) )
        var dateStart = CONVERT( int( datetimeStart ) , DATETIME )
        var datetimeEnd = CALCULATE( MAX( 'Branch data'[end date] ) )
        var dateEnd = CONVERT( int( datetimeEnd ) , DATETIME )
        var numberWeekendHolidayInSeconds = 
            COUNTROWS(
                FILTER(
                    'calender'
                    , ( 'calender'[Date] >= dateStart && 'calender'[Date] <= dateEnd )
                    && ( 'calender'[IS_Workingday] = "Weekend" || 'calender'[IS_Holiday] = "Holiday" )
                )
            ) * 24 * 60 * 60 
        var dateDiffSeconds = DATEDIFF( datetimeStart , datetimeEnd , SECOND ) - numberWeekendHolidayInSeconds
        return
        dateDiffSeconds
    )

//formatting the resutlt
var noSecondS = 60
var noSecondsPerHour = noSecondS * 60
var noSecondsPerDay = noSecondsPerHour * 24
var _Days = TRUNC(DIVIDE(durationInSeconds , noSecondsPerDay ) )
var RemainingSecondsFromDay = MOD( durationInSeconds , noSecondsPerDay )
var _Hours = TRUNC(DIVIDE( RemainingSecondsFromDay , noSecondsPerHour ) )
var RemaingSecondsFromHour = MOD( RemainingSecondsFromDay , noSecondsPerHour )
var _Minutes = TRUNC(DIVIDE( RemaingSecondsFromHour , noSecondS ) )
var RemainingSecodndsFromHour = MOD( RemaingSecondsFromHour , noSecondS )
return
// IF( _Days = 0

    // , _Hours & "h " & _Minutes & "min " & RemainingSecodndsFromHour & "s"

     _Days & " Day " & _Hours & " hr " & _Minutes & " mins " & RemainingSecodndsFromHour & " sec"

// )

    returns what you are looking for:

    Please check the expected result for the 1st row in your sample data, I'm almost sure that the result is correct.

    Hopefully, this provides what you are looking for.

     

    Regards,

    Tom

10 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , A new column

     

    New column =
    var _diff = datediff([end Date], [Start Date], second)
    return
    quotient( _diff,86400) &" Days " & quotient(Mod( _diff,86400), 3600) &" Hours " & quotient(Mod(Mod( _diff,86400), 3600),60) &" Minutes " & mod(Mod(Mod( _diff,86400), 3600),60) &" Seconds "

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi amitchandak ,

       

      Thanks for reply
      But need to exclude weekend dates which is fall in between start date- end date. ?

      • TomMartens's avatar
        TomMartens
        Super User
        3 years ago

        Hey Anonymous ,

         

        take the time to create a pbix that contains sample data, some records and your calendar table. Upload the pbix to onedrive or dropbox and share the link. If you are using Excel to create the sample data share the xlsx as well.

        Also, provide information if it will be possible if a start date and end date can either be a weekend or a holiday.
        Next provide information about the format you want the result if a duration spans more than one day or less than one day. 

        Regards,

        Tom

  • Samarth_18's avatar
    Samarth_18
    Community Champion
    4 years ago

    Hi Anonymous ,

     

    Please try this:-

     

    Working days = 
VAR t1 =
    CALENDAR ( [start date], [End date] )
VAR t2 =
    FILTER (
        ADDCOLUMNS (
            t1,
            "IsWorkDay_", WEEKDAY([Date],2)>5[Date] )
        ),
        [IsWorkDay_]
    )
VAR Days_ =
    COUNTROWS ( t2 )
VAR vSeconds = DATEDIFF('Table (2)'[start date],'Table (2)'[End date],SECOND) - (Days_* 1440*60)
var vMinutes=int( vSeconds/60)
var vRemainingSeconds=MOD(vSeconds, 60)
var vHours=INT(vMinutes/60)
var vRemainingMinutes=MOD(vMinutes,60)
var vDays=INT(vHours/24)
var vRemainingHours=MOD(vHours,24)
return
  vDays&" Days & "&
  vRemainingHours&" Hours & "&
  vRemainingMinutes&" Minutes & "& 
  vRemainingSeconds& " Seconds"

     

     

    Thanks,

    Samarth

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Samarth_18 and amitchandak ,

       

      i have calender table which is contains workingday , Holiday column will update automatically so based on filter we have to write DAX function to get "Day ,Hr, Mins , sec "



      Hence i have written below dax but it's calculating total hours along with days kindly correct me if wrong
      MY DAX Col:

      VAR start_date = 'Table 1’[start date]

      VAR end_date = 'Table 1’[end date]

      VAR T1 = CALENDAR(DATE(2022,01,01),DATE(2022,12,31))

      VAR T2 =FILTER(calender,calender[IS_Workingday] = "Working day" && calender[IS_Holiday] = "Working Day" && calender[Date] >=start_date && calender[Date] <= end_date)

      VAR nodays =COUNTROWS(T2)

      VAR Timediff = TIMEVALUE(end_date-start_date)

      VAR noHrs = HOUR(Timediff)

      VAR noMIN =MINUTE(Timediff)

      VAR noSEC = SECOND(Timediff)

      Return

      nodays & " Day(s) " & noHrs & ":" & noMIN & ":" & noSEC


      Result shows:


      which is wrong be seeing.

       

      thanks