Find the number of working days between clients appointments of different types

[Elisa112]

Hello, I am working on a KPI to report on the number of working days between clients booked appointments of different types, here is an example of the data:

As time goes on each client will have several appointments however I need to report on the most 3 or 4 recent appointments, regardless of whether they are attended or not, and regardless of type, so the output should be something like this:

Im not sure what the most effective solution would be i.e indexing the data or create a summarized table? Either of which I could do with an example

thank you in advance

[v-heq-msft]

Hi @Elisa112 ,

Here some steps that I want to share, you can check them if they suitable for your requirement.

Here is my test data:

1.Create a custom column Group the tables by UserID and then sort them by Date within the group.

Ranking =
RANKX (
    FILTER ( 'Table', 'Table'[UserID] = EARLIER ( 'Table'[UserID] ) ),
    'Table'[Date],
    ,
    ASC,
    DENSE
)

2.Create a new table and get the time of the first, second and third meeting.

SummaryTable = 
SUMMARIZECOLUMNS(
    'Table'[UserID],
    "1st Meeting",CALCULATE(
        MIN('Table'[Date]),
        FILTER(
            'Table',
            'Table'[UserID] = SELECTEDVALUE('Table'[UserID])&&'Table'[Ranking] =1)
    ),
    "2nd Meeting",CALCULATE(
        MIN('Table'[Date]),
        FILTER(
            'Table',
            'Table'[UserID] = SELECTEDVALUE('Table'[UserID])&&'Table'[Ranking] =2)
    ),
    "3nd Meeting",CALCULATE(
        MIN('Table'[Date]),
        FILTER(
            'Table',
            'Table'[UserID] = SELECTEDVALUE('Table'[UserID])&&'Table'[Ranking] =3)
    )
)

3.Create  new calculation columns to calculate the difference in working days between dates.

WorkingDayDiff =
VAR StartDate = SummaryTable[1st Meeting]
VAR EndDate = SummaryTable[2nd Meeting]
VAR WorkingDays =
    COUNTROWS (
        FILTER ( CALENDAR ( StartDate, EndDate ), WEEKDAY ( [Date], 2 ) < 6 )
    )
RETURN
    WorkingDays

WorkingDay Diff = 
VAR StartDate = SummaryTable[2nd Meeting]
VAR EndDate = IF(
    SummaryTable[3nd Meeting] = BLANK(),
    DATE(2023,12,31),
    SummaryTable[3nd Meeting]
)
VAR WorkingDays = 
    COUNTROWS(
        FILTER(
            CALENDAR(StartDate, EndDate),
            WEEKDAY([Date], 2) < 6
        )
    )
RETURN
    WorkingDays

4.Because the parameters in the calender function can not be empty, so it gives a fixed value, and finally after the creation of a column filter can be used to filter abnormal data

5.Final output

Best regards

Albert He

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

[Elisa112 · Accepted solution]

Hi @v-heq-msft 

Thank you for this, I think this is what I am looking for, all was working well except the summary table is showing this output

As you can see the date for 2nd meeting is the same for all users. Which is strange as the ranking function is working fine for 1st and 3rd meetings. I cannot see any difference in the DAX so not sure what is happening.

Also I will always need to find the most recent 3 dates (which could be 9,10, 11 for one user and 1,2,3 for another.

Any further help greatly appreciated, I believe this will work with a few tweeks, 

Thanks again