Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
HI Folks
need your help to improve the below formula to measure 2 different work hours in specified month if i working hours from 9 AM to 3 PM and to excluding public holiday
ifmet = VAR sla2 = DATEDIFF ( Time[start], Time[end], SECOND ) RETURN IF ( WEEKDAY ( Time[start],2 ) IN { 5, 6 }, "done", IF ( TIMEVALUE ( Time[start] ) < TIME ( 9, 0, 0 ) || TIMEVALUE ( Time[end] ) > TIME ( 18, 0, 0 ), "done", IF ( sla2 <= 5400, "done", IF(Value[Value]>=5000,"done","Failed" ) ) ))
small hits : just fourmal are measured normal working time and excluding weekends only
thanks in advanced
Solved! Go to Solution.
Hi @shado26,
To exclude the public holidays in your formula, a holiday table including all holidays may be needed to be added to your model. Then you should be able to follow steps in this thread to add the [If work day] column in your time table.
If work day = IF(OR(DateTable[WeekDay]=1,DateTable[WeekDay]=7),0,IF(ISBLANK(DateTable[Holiday]),1,0))
And add it to your formula like below.
ifmet = VAR sla2 = DATEDIFF ( Time[start], Time[end], SECOND ) RETURN IF ( Time[If work day]<>1, "done", IF ( TIMEVALUE ( Time[start] ) < TIME ( 9, 0, 0 ) || TIMEVALUE ( Time[end] ) > TIME ( 18, 0, 0 ), "done", IF ( sla2 <= 5400, "done", IF(Value[Value]>=5000,"done","Failed" ) ) ))
Regards
Hi @shado26,
To exclude the public holidays in your formula, a holiday table including all holidays may be needed to be added to your model. Then you should be able to follow steps in this thread to add the [If work day] column in your time table.
If work day = IF(OR(DateTable[WeekDay]=1,DateTable[WeekDay]=7),0,IF(ISBLANK(DateTable[Holiday]),1,0))
And add it to your formula like below.
ifmet = VAR sla2 = DATEDIFF ( Time[start], Time[end], SECOND ) RETURN IF ( Time[If work day]<>1, "done", IF ( TIMEVALUE ( Time[start] ) < TIME ( 9, 0, 0 ) || TIMEVALUE ( Time[end] ) > TIME ( 18, 0, 0 ), "done", IF ( sla2 <= 5400, "done", IF(Value[Value]>=5000,"done","Failed" ) ) ))
Regards
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
97 | |
82 | |
75 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |