Forum Discussion
Business hours with empty column using current date
Hello all,
I am aware of the solution where business hours get calculated using existing data. However, is there a solution for getting a lead time in business hours when one of the fields, at times, is empty? Is there a way where DAX/PowerBI can recognise that the empty values for enddate need to be <today>.
As in, today it calculates the business hours using today's date/time, tomorrow tomorrows etc. While also taking in account that workdays are only from monday - friday 08:30 - 17:00 and that holidays (ex. 31-12-xxxx) should be skipped.
For context: This is for helpdesk tickets so at times, when a ticket hasn't been completed, there is no set end date. However, the lead time of all open tickets is an important KPI.
Example of how the data is stored:
startdate enddate
24-05-2021 23:52:00 25-05-2021 12:00:00
14-03-2021 20:02:00
24-05-2021 23:52:00 25-05-2021 12:00:00
19-05-2021 19:00:00 25-05-2021 12:00:00
16-03-2001 20:02:00
etc.
Hi Anonymous ,
First replace the null value in "enddate" column with the following code in Power Query.
#"Replace Value" = Table.ReplaceValue(#"Changed Type", null, DateTime.LocalNow(), Replacer.ReplaceValue, {"enddate"})Then select columns "startdate" and “enddate” respectively, and add new columns containing the time.
Result is as follow. Then you can try https://exceleratorbi.com.au/calculating-business-hours-using-dax/ ,
this splitting of time and date continue when new tickets get added.
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
WinnizIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
Hey Anonymous ,
you can check if the value is blank and if that's the case you can replace it with today:
New enddate = IF(myTable[enddate] = BLANK(), TODAY(), myTable[enddate] )If you need any help please let me know.If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍Best regardsDenisBlog: WhatTheFact.biFollow me: twitter.com/DenSelimovicThanks, this helped! However, I tried one of the known solutions (https://community.powerbi.com/t5/Desktop/Calculating-Working-hours/td-p/374255 ) but it only gave an error saying An argument of function 'TIME' has the wrong data type or the result is too large or too small.
I also tried https://exceleratorbi.com.au/calculating-business-hours-using-dax/ but he works out of an excel file where he splits the date and time. Given it's a static file (and not one that continuously updates like in my case) does this splitting of time and date continue when new tickets get added?
Hi Anonymous ,
First replace the null value in "enddate" column with the following code in Power Query.
#"Replace Value" = Table.ReplaceValue(#"Changed Type", null, DateTime.LocalNow(), Replacer.ReplaceValue, {"enddate"})Then select columns "startdate" and “enddate” respectively, and add new columns containing the time.
Result is as follow. Then you can try https://exceleratorbi.com.au/calculating-business-hours-using-dax/ ,
this splitting of time and date continue when new tickets get added.
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
WinnizIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
In the Query Editor, go to Add Column > Custom Column and add this formula there
=if [enddate]=null then DateTime.Date(DateTime.LocalNow()) else [enddate]
Hope this helps.
