Business hours with empty column using current date

[Anonymous]

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.

[Ashish_Mathur]

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.

[selimovd]

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 regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[Anonymous]

Thanks, 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? 

[v-kkf-msft · Accepted solution]

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,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello @v-kkf-msft , thank you for the explanation and possible solution, apologies for the late reply.

I tried adding the code you posted but I got the following error 

To circumvent this I tried adding changed types with the correct table names (which doesn't give any errors.) This didn't give any errors but the table (completionDate) was still filled with null/blanks. 

Any idea how to solve this? 

[v-kkf-msft]

Hi @Anonymous ,

Try to make the following changes to the lines #"Changed Type"/#"Replace Value"/in.

    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"callDate", type datetime}, {"completionDate", type datetime}}),
    #"Replace Value" = Table.ReplaceValue(#"Changed Type", null, DateTime.LocalNow(), Replacer.ReplaceValue, {"completionDate"})
in
    #"Replace Value"

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,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you, this did the trick!