Ranking dates for each unique ID

[tkeelan]

I have a table that has id's and service dates.  I want to be able to identify the earliest date of service, but also the next date of service for each id.  Ultimately I want to measure the time between the first and second service dates, and count the number of times that time period is less than 90 days.

Can anyone help me?  I have been able to group my query into inique ID and min dates, but I don't know how to takle it in a way that I can get the next date from there.

Thanks!

[v-yuta-msft]

@tkeelan ,

Could you please share the sample data and expected result?

Regards,

Jimmy Tao

[DouweMeer]

I prefer DAX. 

What I did is to look in the record for the future date. Thus the minimum of the dates in the future of its own record. You can then create an additional column where if ( date future - date record < 90 , 1 , blank ). 

Future date =

VAR a1 = SELECTCOLUMNS( 'Service' , "CaseId" , [CaseId] , "Created" , [CreatedDate] )

VAR a2 = SELECTCOLUMNS( filter ( a1 , [CaseId] = 'Service'[CaseId] && [Created] > 'Service'[CreatedDate] ) , "Created2" , [Created] )

RETURN

Minx ( a2 , [Created2] )

Does this help out @tkeelan ? 

[tkeelan]

This is exactly in line with what i'm trying to do @DouweMeer .  Thank you very much.

In my attempt to execute your formula, the formula was taken, although it returned all blanks.  Maybe you can help me a bit further - here's my dax:

Next Service Date = VAR a1 = SELECTCOLUMNS('Billable Progress Notes', "ID - Patient Chart", 'Billable Progress Notes'[ID - Patient Chart], "Future Date of Service", 'Billable Progress Notes'[Date Of Service])

VAR a2 = SELECTCOLUMNS(FILTER(a1,'Billable Progress Notes'[ID - Patient Chart] = 'Billable Progress Notes'[ID - Patient Chart]&& [Date Of Service] > 'Billable Progress Notes'[Date Of Service]), "Future Date of Service", [Date Of Service])

RETURN

MINX(a2, [Future Date of Service])

Also: here's a picture of the table with the blank next service dates.  I want to get the table to show that for "ID-Patient Chart" 10 and "ID-Progress Note" 117587" that the date of service is 1/31/2018, and then the "Next Service Date" should be 2/15/2018 coming from 2/15/2018 from the next note for that patient.

From there I have very comfortable running the DATEDIFF.

Thank you so much! This solves a huge issue for me if I can get there.

[DouweMeer]

@tkeelan 

You can check your tables (VAR table references) by creating a custom table and pasting those VAR's as the table reference. Then you can see what you're working with. Pick a specific recording and use:

Test table = Selectcolumns ( filter ( 'Billable Progress Notes' , 'Billable Progress Notes'[ID-Patient Chart] = "10" ) , 'Billable Progress Notes'[ID - Patient Chart], "Future Date of Service", 'Billable Progress Notes'[Date Of Service])

The only reason to return blank, would be that VAR a2 is empty. 

Also, my expression looks a bit different as I've brought in another identificator into the mix. This as VAR a1 is returning the entire table and will only show the column you select. VAR a2 does the same trick, but as VAR a1 is created, it remains the same. Thus the implicit values of the record won't further affect the context applied in VAR a1 when using it in a2. Conclusion, all fields that will make an amount of records in your tables unique for the thing you're trying to find as the 'future first', should be brought in as well. 

Besides, if you can't make it work, create a custom table of itself and then make a calculate over that table. The reason for this is the implicit filters coming from the record itself. You have somehow have to find a way to seperate both. When I made mine work, I was quite excited. 

Good luck :).