Filtered Days Between

[BrianJ]

@gkavesh ,

There’s three components to the solution, and we’ve got good existing content on each one that should provide you what you need:

1. 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:

DAX Row minus Previous Row

@Archer, This one ended up being pretty straightforward. We just needed to take our previous measure and add the following filter conditions in the PrevDate variable. [image] If we look at 5/23 - 5/26 below, we can see that this is working as expected. Monday picks up the Friday value, but in this case so does Tuesday, since Monday is a holiday (Memorial Day - I’m assuming US holidays, but if you plug in your holiday table column parameter into the extended date table, it will work regardle…

2. 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.

3. 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:

Fixing Card Visual Results In Power BI

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 hope this is helpful.

– Brian