Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 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,...
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

amitchandak's avatar
amitchandak
Super User
3 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
3 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

      • TomMartens's avatar
        TomMartens
        Super User
        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