Forum Discussion
Working Hours Between two date/time fields
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Hi lbendlin,
I found an issue with my data, it appears that the assigned time field may be blank at times. I also simulated with the Work hours.pbix. How can we take this into consideration?
Thank you for your support.
You can choose to ignore these rows, or replace the blanks with an appropriate date. Not sure if "Today" is appropriate for this?
Hey lbendlin,
So I added Now() to the blank field and this worked well.
However, now I observed the root of the issue, I have a few instances were the created date is later than the assigned date, I'm not sure why this would have occured.
But, do you have any recommendations to the script to exlucde these instances?You could use ABS() to always get a positive duration.
Ok noted, just to be clear, I should replace CALENDAR() with ABS()?
var workingdays = FILTER(CALENDAR(MIN([Created],[Assigned]),MAX([Created],[Assigned])),WEEKDAY([Date],2)<6)