cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Ranking dates for each unique ID

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!

4 REPLIES 4
Highlighted
Post Prodigy
Post Prodigy

Re: Ranking dates for each unique ID

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 ? 

Highlighted
Community Support
Community Support

Re: Ranking dates for each unique ID

@tkeelan ,

 

Could you please share the sample data and expected result?

 

Regards,

Jimmy Tao

Highlighted
Frequent Visitor

Re: Ranking dates for each unique ID

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.

 

Future Date of Service.PNG

 

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.

 

Highlighted
Post Prodigy
Post Prodigy

Re: Ranking dates for each unique ID

@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 :). 

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors