Average Between Dates with Datediff?

[gkavesh]

I’ve been able to do dates between to calculate the average time between appointments but now need to figure out the ‘average’ start to end of the length of a clients stay within the clinic.
I’m sure there’s a post out there somewhere that answers this but I’ve read & watched videos nonstop for 2 days and have yet to figure out how to get the average of a datediff calculation w/o adding a custom column.

Unfortunately the raw data of discharge populates when paperwork is completed vs last visit day thus am forced to compare discharge data to full appointment data.

There is no direct link to show which appointments in the appt table are discharged which is why there’s a created dischargeid in both tables.

I’ve got the datediff element working (on the real pbix vs this sample post) but for the life of me I can’t figure out how to get to the average ‘length’ of stay - aka average of the datediff.

DaysBetweenAverage.pbix (169.2 KB)

I feel like I’m oh so close but now going in circles.

I appreciate the help.

gina

[Keith]

I"m wondering how your going to calculate the length of time a client stays within the clinic when you don’t have a start time and end time (in minutes or hours)

I must be missing something in your info.

thanks
Keith

[gkavesh]

Length stay for this purpose is defined as total number of days from initial appointment until discharged as ‘treatment’ has been completed (or insurance ran out or for whatever reason client stopped attending physical therapy appointments. Thus minutes, hours aren’t a needed element. Only how many visits were involved which a visit = unique day as one can’t receive treatment on same case multiple times in one day.
That help?

[Greg]

Hi @gkavesh. To get a non-zero result in your [Average …] measure, make your virtual table reflect the physical table on display (i.e., use the [Discharge] table as the source), then use the ALL function to remove the row context, perhaps something like

---

Average Discharge Visits 2 = 
VAR _vTable = ADDCOLUMNS(
    SUMMARIZE( ALL( Discharge ), Discharge[DischargeId] ),
    "@FirstApptDate", [First Appt Date],
    "@LastApptDate", [Last Appt Date],
    "@DaysBetween", [Days Between]
) 
VAR _Result = AVERAGEX( _vTable, [@DaysBetween] ) 

RETURN
_Result

---

This calculation gives an average for each discharge reason, but I’m guessing this is not what you’re looking for. An Excel mockup of the desired outcome would be a good idea if further pursuit is needed.

Hope this helps.
Greg
eDNA Forum - Average Between Dates.pbix (169.1 KB)

[gkavesh]

Thanks -
I’m looking for the average days from first appt to last appointment across all appointments and then down to the discharge reason…

Similar to this image

Which I managed to get the 72.70 by adding a calculated column of the discharge difference to the discharge table and then applied an averagex to that column.

BUT I realize adding custom columns is not the best methodology so thinking the virtual table path would be better - I’ll try your idea to see how it lines up.

[ankit]

Hi @gkavesh

Based on your requirement description it seems you want to calculate Total of DateDiff/Count of Discharge. For this, try using below function…

AverageDischargeVisits = 
 
VAR vTable =
    SUMMARIZE(
        ALLSELECTED(Discharge),[DischargeID],
    "@FirstAppt", [FirstApptDate],
    "@LastApt", [Last Appt],
    "@daysbtwn", [DaysBetween]
)

RETURN
AVERAGEx(vTable,[@daysbtwn])

I am getting an Average of 88.88, not sure how are you getting 72.70. Let me know if you see any diff and I can work on it.

Attached the Solution
DaysBetweenAverage_Ankit.pbix (188.2 KB)

Thanks
Ankit J

[gkavesh]

Thank you -
The reason for the difference is that I’m using the ‘real’ data while the data I used for this question was a small selection of the data that was changed due to it’s confidential nature.

This solution works and avoids the column add in the actual data.

[deltaselect]

Hi @gkavesh ,

This one measure solution might also help you further:
(Building further on Greg’s solution, and using the EDNA video about correcting erroneous totals)
eDNA DS - Average Between Dates.pbix (169.5 KB)

Avg. Discharge Duration v3 =
AVERAGEX(
SUMMARIZE( Discharge, Discharge[DischargeId], // from table Dischange
“First Visit”, [First Appt Date], // from table AllApts
“Last appoint”, [Last Appt Date]), // from table AllApts
DATEDIFF( [First Appt Date], [Last Appt Date], DAY)) // this calculates the correct total average

Principle of total correction learned from :

Fixing errors with your totals efficiently