Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Folks,
i have two dates with time (start date , end date) need to calculate how long it tooks complete the activity except weekends and holidays in calender table working day, holiday column is available . 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.
i tried below cal. column but counting days, total hours as well kindly correct me this DAX:
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
wrong output on above code example :
Kindly help on this
Thanks,
MS
Solved! Go to Solution.
Hi @MSuser5 ,
I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can create a calculated column as below:
Activity took =
VAR _sdate =
DATE ( YEAR ( 'Table 1'[start date] ), MONTH ( 'Table 1'[start date] ), DAY ( 'Table 1'[start date] ) )
VAR _edate =
DATE ( YEAR ( 'Table 1'[end date] ), MONTH ( 'Table 1'[end date] ), DAY ( 'Table 1'[end date] ) )
VAR _sstart =
DATEVALUE ( ( _sdate + 1 ) & " " & TIME ( 0, 0, 0 ) )
VAR _eend =
DATEVALUE ( ( _edate ) & " " & TIME ( 0, 0, 0 ) )
VAR _wehdates =
CALCULATETABLE (
VALUES ( 'calender'[Date] ),
FILTER (
'calender',
'calender'[IS_Workingday] = "Weekend"
|| 'calender'[IS_Holiday] = "Holiday"
)
)
VAR _s1duration =
IF (
_sdate IN _wehdates,
0,
DATEDIFF ( 'Table 1'[start date], _sstart, SECOND )
)
VAR _e1duration =
IF (
_edate IN _wehdates,
0,
DATEDIFF ( _eend, 'Table 1'[end date], SECOND )
)
VAR _duration =
IF (
_sdate = _edate,
DATEDIFF ( 'Table 1'[start date], 'Table 1'[end date], SECOND ),
_s1duration + _e1duration
)
VAR _minutes =
INT ( _duration / 60 )
VAR _RemainingSeconds =
MOD ( _duration, 60 )
VAR _hours =
INT ( _minutes / 60 )
VAR _RemainingMinutes =
MOD ( _minutes, 60 )
VAR _days =
INT ( _hours / 24 )
VAR _RemainingHours =
MOD ( _hours, 24 )
VAR _bdates =
CALCULATE (
DISTINCTCOUNT ( 'calender'[Date] ),
FILTER (
'calender',
'calender'[IS_Workingday] = "Working day"
&& 'calender'[IS_Holiday] = "Working day"
&& 'calender'[Date] > _sstart
&& 'calender'[Date] < _eend
)
)
RETURN
IF ( _sdate = _edate, 0, ( _bdates + _days ) ) & " Day(s) " & _RemainingHours & " hr(s) " & _RemainingMinutes & " min(s) " & _RemainingSeconds & " sec(s)"
Best Regards
Hi @MSuser5,
You can refer the following links to get it:
How To Get The Duration Between Two Datetime Or Date Fields In Power BI
Hi Folks,
Could anyone fix this issue in using calculated column DAX
Thanks,
MS
Hi @MSuser5 ,
I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can create a calculated column as below:
Activity took =
VAR _sdate =
DATE ( YEAR ( 'Table 1'[start date] ), MONTH ( 'Table 1'[start date] ), DAY ( 'Table 1'[start date] ) )
VAR _edate =
DATE ( YEAR ( 'Table 1'[end date] ), MONTH ( 'Table 1'[end date] ), DAY ( 'Table 1'[end date] ) )
VAR _sstart =
DATEVALUE ( ( _sdate + 1 ) & " " & TIME ( 0, 0, 0 ) )
VAR _eend =
DATEVALUE ( ( _edate ) & " " & TIME ( 0, 0, 0 ) )
VAR _wehdates =
CALCULATETABLE (
VALUES ( 'calender'[Date] ),
FILTER (
'calender',
'calender'[IS_Workingday] = "Weekend"
|| 'calender'[IS_Holiday] = "Holiday"
)
)
VAR _s1duration =
IF (
_sdate IN _wehdates,
0,
DATEDIFF ( 'Table 1'[start date], _sstart, SECOND )
)
VAR _e1duration =
IF (
_edate IN _wehdates,
0,
DATEDIFF ( _eend, 'Table 1'[end date], SECOND )
)
VAR _duration =
IF (
_sdate = _edate,
DATEDIFF ( 'Table 1'[start date], 'Table 1'[end date], SECOND ),
_s1duration + _e1duration
)
VAR _minutes =
INT ( _duration / 60 )
VAR _RemainingSeconds =
MOD ( _duration, 60 )
VAR _hours =
INT ( _minutes / 60 )
VAR _RemainingMinutes =
MOD ( _minutes, 60 )
VAR _days =
INT ( _hours / 24 )
VAR _RemainingHours =
MOD ( _hours, 24 )
VAR _bdates =
CALCULATE (
DISTINCTCOUNT ( 'calender'[Date] ),
FILTER (
'calender',
'calender'[IS_Workingday] = "Working day"
&& 'calender'[IS_Holiday] = "Working day"
&& 'calender'[Date] > _sstart
&& 'calender'[Date] < _eend
)
)
RETURN
IF ( _sdate = _edate, 0, ( _bdates + _days ) ) & " Day(s) " & _RemainingHours & " hr(s) " & _RemainingMinutes & " min(s) " & _RemainingSeconds & " sec(s)"
Best Regards
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |