I want to show patients whose difference in first and second dose of vaccination is more than 30 day

[rabiafarooq]

i have a patient vaccination info table with dose numbers in a column. i want to display those medical record numbers(MRN) for which the difference in first and second dose is more than 30 days.

Attached image shows the table structure

[corbusier]

Are you interested in the difference between the first and last dates, or between the first and second dates? MIN and MAX would give you the former, not the latter. 

[rabiafarooq]

@corbusier difference between screening date and first date, first date and second date, second date and third date.

[Salonic · Accepted solution]

Hi,

I don't know if this is the best way but you could create a new table with GROUPBY where you extract the first date and the second date.

Here is the code

Delta Dose = 
GROUPBY (
   UNION(
      GROUPBY(
        FILTER('Nursing Vaccination';'Nursing Vaccination'[Dose #]="First");
        'Nursing Vaccination'[Patient Name];
        "First Date";MAXX(CURRENTGROUP();'Nursing Vaccination'[Immunization Date]); 
        "Second Date";MINX(CURRENTGROUP();0))
       ;
      GROUPBY(
        FILTER('Nursing Vaccination';'Nursing Vaccination'[Dose #]="Second");
        'Nursing Vaccination'[Patient Name];
        "First Date";MINX(CURRENTGROUP();0);
        "Second Date";MINX(CURRENTGROUP();'Nursing Vaccination'[Immunization Date]) )
   );
   'Nursing Vaccination'[Patient Name];
   "First Date";MAXX(CURRENTGROUP();[First Date]);
   "Second Date";MAXX(CURRENTGROUP();[Second Date])

)

You can then create a relationship with this new table and the data table to extract all the lines with more than 30 days.

With a file :

https://1drv.ms/u/s!AhxEamX-j2-mgmdo5os_0LHJ8qmZ

Hope this will help,

[rabiafarooq]

@Saloni what if we have have four dates? and we want difference between screening to first dose, first to second dose  , second to third dose?

[rabiafarooq]

Worked like a charm. THANK YOU SO MUCH.

[Anonymous]

@rabiafarooq

I used my own sample dataset and tried it. Below is data I used -

After this, I created 3 columns as below -

SecondVisitDate = CALCULATE ( MAXX( FILTER(Patients,Patients[Visit#]="Second"),Patients[Date]) , ALLEXCEPT ( Patients, Patients[Name] ) )

FirstVisitDate = CALCULATE ( MAXX( FILTER(Patients,Patients[Visit#]="First"),Patients[Date]) , ALLEXCEPT ( Patients, Patients[Name] ) )

# of Days = DATEDIFF(Patients[SecondVisitDate],Patients[FirstVisitDate],DAY)

The last column above "# of Days" will give a number which is less than -30 in case patient had a difference of more than 30 days between first and second visit.

Regards

[rabiafarooq]

@AnonymousSo i am getting something like this. what does the very high negative and positve values mean?

[Anonymous]

@rabiafarooq

Do you mind sharing your measure?

Ideally, as SecondVisitDate is always greater than (i.e. after) FirstVisitDate, so it should not give you positive values in the measure.

Regards

[rabiafarooq]

@AnonymousHere is the measure. Also i am getting wrong output dates. please see the 2nd image. its for the same patient.