calculate difference between two dates using DAX

[Anonymous]

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

[Samarth_18]

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]

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

[amitchandak]

@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]

Hi @amitchandak ,

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

[TomMartens]

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

[Anonymous]

Hi @TomMartens ,
Thanks, i have placed sample pbix file please access using below link
https://drive.google.com/file/d/16yOVXy_Se34eGRpVMuzAVURXowNWf4DF/view?usp=drivesdk

Thanks,

MS

[Anonymous]

Hi Tom,
Thanks, i have placed sample pbix file please access using below link
https://drive.google.com/file/d/16yOVXy_Se34eGRpVMuzAVURXowNWf4DF/view?usp=drivesdk

Thanks,

MS

[TomMartens · Accepted solution]

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

[Anonymous]

Hi @TomMartens ,

Great! it's working fine 

Thanks,

MS

[Anonymous]

Hi @TomMartens ,

Thanks for your effort it's working fine as expected but i have to create this same in calculated column because i'm unable to refer this measure to another calculated column.

so is it possible to create same calc column or can we use this measure in another calculated column?

Thanks,

MS