Forum Discussion
Wait Time Calculation
I'm trying to work out wait times and be able to work out average wait times and Graph
I have the table set up with Arrival time and Consultation time see below
I have created a custom Column called wait time which is consultationtime - Arrivaltime, The results are shown below, there's no issue with the results this column gives me, the issue comes when I try to find min and max or graph, it completely ignores the hours and just graphs the minutes (eg the 2hr 18 min result is just seen as 18 min). this then throws out the wait times and reports incorrectly.
I've tried setting up the Wait time column using Datediff, however this formula isn't recognised for some reason, I've tried using time.minute, this just gives me the minutes ignoring the hours, similar if I used time.hour just gives me the hours and ignores the minutes.
Happy if it just gave me the reading with just the minutes eg. 1:30 just came back as 90 etc
Oh OK, DATEDIFF is a DAX function used in PowerBI. You're trying to add a 'custom column' in PowerQuery not a 'calculated column' in PowerBI itself. The equivalent function to calculate duration in minutes between date/time in PowerQuery would be Duration.TotalMinutes
See the solution proposed here: https://forum.enterprisedna.co/t/return-difference-of-two-dates-in-minutes/15174
Alternatively if your dataset isn't huge then adding this as a calculated column to your table in PowerBI using DATEDIFF is still an option.
6 Replies
DATEDIFF works with date or date/time but I suspect it requires a date.
Try:
= DATEDIFF(TODAY() + [ArrivalTime], TODAY()+[ConsultationTime],MINUTE])
You can substitute the actual date instead of TODAY() if you have it, but shouldn't make a difference if arrival and consultation are always the same day.
For some reason my BI doesn't recognise the DATEDIFF function at all
Oh, that's weird. Are other functions working OK? Ocassionally the intellisense autocomplete prompt might get glitchy and you may need to quit out and reopen. It could just be something contextual if you have other elements in your formula.
You're adding this as a calculated column in the same table yes? Not as a measure.
Maybe post a screenshot of the formula and any error you're getting.
Yes custom Column
Just realised I forgot to start the Square Bracket with the Minutes
When created I get
Then if I replace the formula of the column previously I just get
Oh OK, DATEDIFF is a DAX function used in PowerBI. You're trying to add a 'custom column' in PowerQuery not a 'calculated column' in PowerBI itself. The equivalent function to calculate duration in minutes between date/time in PowerQuery would be Duration.TotalMinutes
See the solution proposed here: https://forum.enterprisedna.co/t/return-difference-of-two-dates-in-minutes/15174
Alternatively if your dataset isn't huge then adding this as a calculated column to your table in PowerBI using DATEDIFF is still an option.Thankyou
the second part of the formulae works.
How do you use DAX functions in BI, there's a few functions that I would like to start using but not sure how to input them as DAX
