I thought I had found a solution to filter out the 3 status types with a new table of excluded status types but the concept errors within the dax equation (prev appt date test).
I’m sure there’s a way to filter out 3 status types (no show, client canceled, clinic canceled) before calculating the days between et al but it’s clearly beyond my existing dax skillset -
There’s three components to the solution, and we’ve got good existing content on each one that should provide you what you need:
Find the previous appointment date, filtering out the excluded status types
This post provides the DAX code for how to do this. The example given excludes Saturday’s, Sundays and holidays, but it’s basically the equivalent of your excluded status types:
Calculate the days difference between the current appointment date and the previous appointment date calculated in #1 above.
You already know how to do this, based on the work you’ve already done.
Calculate the average days between given the filtering of excluded status types
This will require developing a virtual table of the client ID, date and days between to build out the appropriate context, and then force Power BI to average down the days between column. This video explains exactly how to do this:
This should give you what you need to solve this problem, but if you still have issues after reviewing the materials above, just give a shout and we can work through the specific solution together.
I must be dense - I don’t see how this example relates to using the status within the filter statement as the only option that I’m given is the alloperations[date] if I extend to additional filter options with the &&.
If I go down the path of adding another All filter DAX barfs all over it.
Or do I need to make another variable actual appointment date using the status filter and then use this date variable for the current/previous calculations?
No, not dense at all. This is a tricky measure, but you were definitely on the right track. One thing I needed to do here was to add a check on the current client so that the previous date didn’t roll to a different client, but stayed within the same name or returned a blank if there was no prior date. Also, your Excluded table was not a proper dimension table, so I broke the relationship with the fact table and just left it as a disconnected table.
Prev Appt Date Rev =
var CurrentVisitDate = SELECTEDVALUE( DateTable[Date] )
VAR CurrentClient = SELECTEDVALUE( AllOperations[Client] )
VAR ExcludedReasons = {"No Show", "Patient Canceled", "Center Canceled" }
VAR Result =
CALCULATE(
MAX( DateTable[Date] ),
FILTER(
ALL( AllOperations ),
AllOperations[Client] = CurrentClient &&
AllOperations[Date] < CurrentVisitDate &&
NOT( TRIM( AllOperations[Status]) IN ExcludedReasons )
)
)
RETURN
Result
Based on the revisions to the measure above, I also updated the Average Days between measure, which you also were very much on the right track on.
Avg Days Between =
VAR vTable =
ADDCOLUMNS(
SUMMARIZE(
AllOperations,
AllOperations[Client],
DateTable[Date],
AllOperations[Status]
),
"@PrevDate", [Prev Appt Date Rev],
"@DaysBtw", [Days Between]
)
VAR Result =
AVERAGEX(
vTable,
[@DaysBtw]
)
RETURN
Result
Here’s what it looks like all put together, and you can see that it’s appropriately skipping the dates associated with excluded reasons:
Note that I changed the filter on which patients were displayed, in order to pick up those with each of the different excluded reasons for testing purposes.
I hope this is helpful. Full solution file attached below.
Very helpful! Thank you so much! I don’t think I would have been smart enough to think about confirming the client stayed with correct appointment.
I had originally started with the excluded table disconnected but connected it while trying different solutions and forgot to remove that attempt.
Slowly getting my arms around the virtual table & variable elements.
