Filter the top two dates for each client date of service

[Anonymous]

I have a table where there is a client ID (1234,2345,3456, etc.). Each client has multiple dates of service. I want to filter out the top two dates of service for each and then calculate the datediff between the two dates that remain. The client_id and service_date are the column names. I've tried indexing, a calculated column, and pulling out my hair. I'm down to my last strand. Please help. Some clients have 5 dates of service while others only have 2. Some have just one and those need to be filtered out completely. 

The top two would be the oldest two. 

[Anonymous]

Any updates on this? Were are you able to solve this problem?

[Ashish_Mathur]

Hi,

Please share some data and show the expected result.

[Anonymous]

Could you use the rankx function and filter on <= 2?

[Anonymous]

I was trying to figure that out, because the filtering I though worked, didn't. How would I rankx for each client's dates of service and not a rankx of all dates of service?

[Anonymous]

Assuming you have a table with two columns, UserId and Date, the following calculated column ranks each date by UserId

Date Rank = RANKX(CALCULATETABLE('Table1', ALLEXCEPT(Table1, Table1[UserId])), 'Table1'[Date], , ASC)

[Anonymous · Accepted solution]

Just to give help you a bit more, you can then get the first and second date using the following measure

First Date = MAXX(FILTER('Table1', [Date Rank] = 1), 'Table1'[Date].[Date])
Second Date = MAXX(FILTER('Table1', [Date Rank] = 2), 'Table1'[Date].[Date])

To get the difference you would then simply use the following and put it against UserId in a table

Date Diff = DATEDIFF([FirstDate], [Second Date], DAY)

Hopefully this helps get you close enough!