Excluding after date using slicer

[DataSkills]

Hi, 

I have a scenario where I have patients who are in a care facility. These patients get various assessments done during their stay such as weight, BMI, blood pressure, cholesterol etc. I display these assessments during a specific month using a slicer which has months like Jan 23, Feb 23 etc to show assessments in Jan 23 or Feb 23 etc. 

However, patients can be discharged or they may pass away and at this time their file is closed on the system. This is recorded in a datetime field called FileClosed in my Patients table. 

When a patient is no longer in the facility, I don't want to show them. Eg if a patient was discharged on 15 Jan 2023, then in the Jan 23 slicer period, I DO want to see them (they were still a patient in Jan 23 and earlier months), but I don't want to see them in Feb 23. 

I am struggling to figure out how I can show a patient before he/she died or was discharged, but exclude them after that point. 

Thanks for guidance. 

[DataSkills · Accepted solution]

Hello @MohammadLoran25 thank you for your suggestion. 

I have solved this another way. What I did was to create a calculated column as follows:

Active = If(calculate(Values(Assessment_Period[EndDate]),

filter(Assessment_Period, Assessment[Months] = Assessment_Period[Months])) > EOMONTH(calculate(Values(Patient[FileClosed]),

filter(Patient, Assessment[PatientId] = Patient[Id])), 0), "No", "Yes"

)

This determines at the row level in the assessment table if that particular assessment is for a patient who is no longer active. 

[MohammadLoran25]

@DataSkills ,

I am not sure because of lack of data. But I think following these steps would solve your problem:

1-Create a measure as below:

PatientFilter =
SUMX (
    FILTER (
        Assesment,
        YEAR ( RELATED ( Patient[FileClosed] ) ) * 12
            + MONTH ( RELATED ( Patient[FileClosed] ) )
            = YEAR ( MAX ( DateTable[Date] ) ) * 12
                + MONTH ( MAX ( DateTable[Date] ) )
            || ISBLANK ( RELATED ( Patient[FileClosed] ) )
            || RELATED ( Patient[FileClosed] ) >= MAX ( DateTable[Date] )
    ),
    1
)

2-Then Put this measure as a filter on your visual in filterpane. (set it to advanced filter, IS GREATER THAN 0).

**Notice that at first your visual shows no value, But once you put date slicer from date table and select a date, it would work and shows the related patients.

If this answer solves your problem, please give it a thumbs up and mark it as an accepted solution so the others would find what they need easier.

Regards,
Loran

[MohammadLoran25]

Hi @DataSkills ,

Would you please clear the relationships between tables? Which columns are related and also what is DateTo column in assesment table?