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

Samarth_18's avatar
Samarth_18
Community Champion
3 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
    3 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