Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I have this following Data, I am trying to find a way to calculating Working hours in betwen dates excluding Weekends.
Works hours are between: Morning 9:00 AM to Evening 6:00 PM and Saturday and Sunday are holidays.
TicketID | ACTIVITY_DATE | LASTMODIFIEDDATE |
86256 | 28-12-2017 03:11 | 28-12-2017 15:11 |
89890 | 15-08-2017 20:23 | 15-08-2017 21:12 |
111611 | 04-10-2017 21:30 | 10-10-2017 13:00 |
111511 | 04-10-2017 02:30 | 10-10-2017 13:00 |
111542 | 04-10-2017 02:41 | 10-10-2017 13:00 |
111485 | 04-10-2017 02:41 | 10-10-2017 13:00 |
211411 | 06-10-2017 14:25 | 10-10-2017 13:00 |
150895 | 10-11-2017 12:32 | 18-01-2018 13:29 |
152996 | 08-08-2017 03:40 | 09-08-2017 11:00 |
any help?
Solved! Go to Solution.
HI @rocky09,
You can try to use below calculated column formula to calculate valid working hour:
Work Hour = VAR filtered = FILTER ( ADDCOLUMNS ( CROSSJOIN ( CALENDAR ( [ACTIVITY_DATE], [LASTMODIFIEDDATE] ), SELECTCOLUMNS ( GENERATESERIES ( 9, 18 ), "Hour", [Value] ) ), "Day of week", WEEKDAY ( [Date], 2 ) ), [Day of week] < 6 && [TicketID] = EARLIER ( Table1[TicketID] ) ) VAR hourcount = COUNTROWS ( FILTER ( filtered, ( [Date] >= DATEVALUE ( [ACTIVITY_DATE] ) && [Hour] > HOUR ( [ACTIVITY_DATE] ) + 1 ) && ( [Date] <= DATEVALUE ( [LASTMODIFIEDDATE] ) && [Hour] > HOUR ( [LASTMODIFIEDDATE] ) - 1 ) ) ) VAR remained = DATEDIFF ( TIMEVALUE ( [ACTIVITY_DATE] ), TIME ( HOUR ( [ACTIVITY_DATE] ) + 1, 0, 0 ), MINUTE ) + DATEDIFF ( TIME ( HOUR ( [LASTMODIFIEDDATE] ) - 1, 0, 0 ), TIMEVALUE ( [LASTMODIFIEDDATE] ), MINUTE ) RETURN IF ( hourcount <> BLANK (), (hourcount*60 + remained)/60, 0 )
Regards,
Xiaoxin Sheng
I tried this solution for the data in SQL server database. I am also getting the same error:
"An argument of function 'TIME' has the wrong data type or the result is too large or too small."
Can some one please help me to replve this
KKMUrthy
Please check your Data Type for the Date Column. It should be Date format.
I ensure the data type as Datetime in query editor. I then changed the datetime format to the one shown in the screenshot
I chose the data type as Datetime for the date fields in query editor. I then changed the datetime format to the one shown in the screenshot in the data tab. I still get the same error
any thoughts please?
replace the comma with a semicolon
Hi
Thank you for spending time on this issue.
I am getting the syntax error: The syntax for ';' is incorrect.
for using ; in place of ,
I am using the power BI Desktop( Aug 2018)
Any further help is greatly appreciated
Did you checked your data type of Date Column in query editor?
Thank you so much sir.
I am getting the below error.
The Start date in Calendar function can not be later thanthe end date.
I guess, the first column may have greater date than modified date. Is there a way to dealth it
Hi @rocky09,
>>I guess, the first column may have greater date than modified date. Is there a way to dealth it
It means your table contains records which start date greater than end date. Datediff function not support calculated with records who have greater startdate(compare with end date).
You can add some conditions to ignore calculation when 'start date' greater than 'end date'.
Notice: DATEDIFF(start date, end date, unit)
Regards,
Xiaoxin Sheng
Hi @v-shex-msft I got a question for you:
I have 2 issues when I use your solution.
1. Error because those fields in calendar can not be blank.
2. Error because sometimes the lastdate is previous to firstdate.
Can you PLEASE give me some solution? I know those are conditions I have to add in the same code you put. But I don't know where to make it work.
Thanks man! I hope you to be fine.
Hi,
I tried the formula above and I get the error message "The start date or end date in Calendar function can not be Blank value". Any way to resolve this?
Thanks
actually, i found the reason,
My Date is changing once loading to data model. Actually, the time is early monring. Is it possible to prevent?
example: from 01-12-2017 01:50 to 01-12-2017 PM 01:50
I got the same error, could you solve it ?
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
87 | |
82 | |
65 | |
63 | |
57 |
User | Count |
---|---|
171 | |
113 | |
110 | |
73 | |
73 |