Schedule Events and Status with Measures

Whitewater100 · February 15, 2021, 2:36pm

Hello Brain and Community:

i was hoping to get an assist on three columns via DAX measures on a table I(PBIX attached) am providing as well as an excel sheet showing the desired results. I am a bit stuck on this last part.

I am trying to get the name and associated dates (time and it’s status) for the next scheduled interview based on time windows. The patient( (named people or consumer) once admitted is supposed to be interviewed every three months. The data is completely mocked up and redacted.

The key tables involved are SPARS(data table) : DATES: ASSESSMENT CODE : & STATUS (has info regarding the last measure of the three requested)

I took this fairly far and the green highlighted part of the excel shows the results I’m hoping to obtain via couple of examples.

Thanks in advance for any help you have on my questions!

Separately, any suggestions on the data model design itself are also welcomed.

Best regards
and thanks very much!
Bill
SPARSR.pbix|attachment](upload://aAg9uf0unuhwzOKX4NndS450l9p.pbix) (243.4 KB) tracker mock-up.xlsx (16.2 KB)

jbressan · February 15, 2021, 11:01pm

Hi @Whitewater100

This is the Status you looking for?

I did with two measures

DurationDays =
VAR _PeopleID =
    SELECTEDVALUE ( SPARS[PeopleID] )
VAR _Filter =
    FILTER ( SPARS, SPARS[PeopleID] = _PeopleID )
VAR _CurrentAssesment =
    SELECTEDVALUE ( SPARS[InterviewDate] )
VAR _InitAssesment =
    CALCULATE ( MIN ( SPARS[Assessment Date] ), ALL ( SPARS ), _Filter ) //Dynamic DueDate
VAR _CountAssesment =
    CALCULATE (
        COUNT ( SPARS[Assessment Date] ),
        ALLEXCEPT ( SPARS, SPARS[PeopleID] ),
        SPARS[InterviewDate] <= _CurrentAssesment
    )
VAR _DueDate =
    IF (
        _CountAssesment = 1,
        _InitAssesment,
        _InitAssesment + ( 90 * _CountAssesment )
    ) //Days
VAR _DurationDays =
    DATEDIFF ( _CurrentAssesment, _DueDate, DAY )
RETURN
    _DurationDays

and

Status =
CALCULATE (
    VALUES ( 'Status'[Status] ),
    FILTER (
        'Status',
        [DurationDays] >= 'Status'[Min]
            && [DurationDays] <= 'Status'[Max]
    )
)

Additionally, I add two columns to Status Table (Min and Max)

image_2021-02-15_175903

SPARSR - jbressan.pbix (245.4 KB)

Whitewater100 · February 16, 2021, 1:48am

Hello JBressan:

That was some excellent work. I think there is a lot of value in the measures you laid out. I wanted to mention a couple of comparison examples in case this makes the request a bit more clear.

On my report I was showing each person’s ID once. With the first assess and last date of the last one.

So for person #1. It’s been a long while since they were seen. Based on their first assess date of 5-15-19 they would be due for their 21 month assessment. (They are due in synch with the assessment table )regardless if they didn’t have any assessments in between.

Another example is ID 567. The next assessment due is the 9 month one as that person already had the 6 month assessment. It’s been say 152 days since first seen and because the open window for the nine month assessment starts at day 147 that person is within the window to give the 9 month assessment.

The next assessment due is lined up with the first assessment data as day one(9-15-20). So the 9 month assessment should occur (Due Date) after nine months. on 6-15-21… It this case of #567, it’s too early as the earlies for the open window would be a month before or 5-51-21. Day 244 starts the open window.

I’m pretty sure I should be able to tweak your measures but if this feedback prompts you to fine tune them, I’m up for that.

Thanks again for taking your time to address my question.

Best regards,
Bill

Whitewater100 · February 16, 2021, 2:22pm

Hi JB and Community:
While JB considers my reply I wanted to put this question back on the topics until it is solved.

I have attached an update and am still looking for the final solution. I’m hoping to get DAX to show both next scheduled assessment and which time window this falls into. The issue is if the next scheduled assessment has been completed ( see person #567) then the following assessment would be the next assessment. I have put in a measure that jerry-rigged a measure to result in the next assessment but I am losing all the wanted results for next assessment. Those measures are side by side in my table at the far end. Person 567 is too early for the 9 month assessment as the beginning window for that is day 244 (Assessment codes table) “Open Window” because it’s only been 155 days (Elapsed time) since the first assessment.

Lastly based on the day count from the first assessment the question is - is today to early to do the next assessment, is it overdue, or is it on track. These two questions are all related to my tables “Assessment Codes” and “Status”.

The Date Due of next assess. is another one I’m hoping to solve. Assessments happen every 3 months with the first assessment date always as the starting point. The six month assessment would have a due date of 182 days from the first assessment.

So Three Measures needed are:
Next scheduled Assessment
Due Date for Assessment
Status on Next Scheduled

Everything else seems to be working fine. I have attached my updates since yesterday.

Thank you.

Bill S

SPARSR.pbix (244.0 KB)

jbressan · February 16, 2021, 3:14pm

Hi @Whitewater100

Is this you looking for? Sample: patient 567

SPARSR - jbressan.pbix (252.7 KB)

Whitewater100 · February 16, 2021, 3:55pm

Hi JB.
Yes that looks great. The last column would be Status. So #567 is “Too Early” for the 9 month assess.

I like the second shot where #567 is just on one line. The report would be based on the people ID’s just showing one record per patient. This looks great though. So thank you!

I believe a lot of records can be filtered out later on as in this example there are a lot of people who aren’t being seen or the people left.

I’m going to download your example file and check it out. I feel like you have done a really nice job on this. I really appreciate you jumping in and helping with your skills.

Thanks again! Bill

Whitewater100 · February 17, 2021, 6:56pm

Hi Jose:
I was hoping to ask a couple of final questions. I brought your measures into my table on page one and the results change. For instance person#567 says next assessment due is 6 month but that assessment has already been completed. #567’s 9 month reassessment would be due 6-15-2021but the report shows March.
On #713 it shows this person seen on 1-25-21 but next assessment shows 1-15-21. It looks like a number of the 700 series have the same issue.
If it makes it any easier, we can filter out the discharges, if that helps at all.

I believe I saw #646-648 who already had three month assessment but with next scheduled assessment as a three month.

Anyways I wanted to ask what you thought might solve the issue when I put the measures into my table?

Thanks in advance for any input to this. I will include the file you did with my examples noted here.

Best regards,

BillSPARSR - jbressan (1).pbix (253.9 KB)

jbressan · February 17, 2021, 7:53pm

Hi @Whitewater100

Update Next Assessment Due Date and Next Assessment Calculated Columns with this

Next Assessment Due Date = 
VAR _FirstDate = [Baseline Assessment Date]
VAR _Today = TODAY()
VAR _Days = DATEDIFF(_FirstDate,_Today,DAY)

VAR _BaseAssessment = LOOKUPVALUE('Assessment Code'[Assessment Code Detail],'Assessment Code'[Assessment Code], 600)
VAR _Assessment_1 =
    CALCULATE(
        SELECTEDVALUE('Assessment Code'[Assessment Code Detail]),
        FILTER('Assessment Code',
        _Days >= 'Assessment Code'[Open Window] && 
        _Days <= 'Assessment Code'[Close Window])
        )
VAR _Assessment = IF(_Assessment_1 = BLANK(), _BaseAssessment,_Assessment_1)

VAR _CheckLastAssessment = IF([Last Assessment] = _Assessment,TRUE(),FALSE())
VAR _Months = LOOKUPVALUE('Assessment Code'[Months],'Assessment Code'[Assessment Code Detail],_Assessment)

VAR _NextAssessmentMonth = 
    IF(_CheckLastAssessment,
        CALCULATE(MIN('Assessment Code'[Months]),
        FILTER('Assessment Code', 'Assessment Code'[Months] > _Months)),
        _Months)
VAR _DueDateCurrentAssessment = _FirstDate + (_Months * 30)        
VAR _DueDateNextAssessment = _FirstDate + (_NextAssessmentMonth * 30)      

VAR _CurrentAssessment = IF(_CheckLastAssessment,_DueDateNextAssessment,_DueDateCurrentAssessment)

Return IF(Pateints[Patient Discharge],BLANK(),_CurrentAssessment)

and

Next Assessment = 
VAR _FirstDate = [Baseline Assessment Date]
VAR _Today = TODAY()
VAR _Days = DATEDIFF(_FirstDate,_Today,DAY)

VAR _BaseAssessment = LOOKUPVALUE('Assessment Code'[Assessment Code Detail],'Assessment Code'[Assessment Code], 600)
VAR _Assessment_1 =
    CALCULATE(
        SELECTEDVALUE('Assessment Code'[Assessment Code Detail]),
        FILTER('Assessment Code',
        _Days >= 'Assessment Code'[Open Window] && 
        _Days <= 'Assessment Code'[Close Window])
        )
VAR _Assessment = IF(_Assessment_1 = BLANK(), _BaseAssessment,_Assessment_1)
VAR _CheckLastAssessment = IF([Last Assessment] = _Assessment,TRUE(),FALSE())
VAR _Month = LOOKUPVALUE('Assessment Code'[Months],'Assessment Code'[Assessment Code Detail],_Assessment)
VAR _NextAssessmentMonth = 
    IF(_CheckLastAssessment,
        CALCULATE(MIN('Assessment Code'[Months]),
        FILTER('Assessment Code', 'Assessment Code'[Months] > _Month)),
        999)
VAR _NextAssessment = LOOKUPVALUE('Assessment Code'[Assessment Code Detail],'Assessment Code'[Months],_NextAssessmentMonth)
VAR _CurrentAssessment = IF(_CheckLastAssessment,_NextAssessment,_Assessment)
Return IF([Patient Discharge],BLANK(),_CurrentAssessment)

SPARSR - jbressanV2.pbix (252.4 KB)

Whitewater100 · February 18, 2021, 2:37am

jbressan:

VAR _FirstDate = [Baseline Assessment Date]
VAR _Today = TODAY()
VAR _Days = DATEDIFF(_FirstDate,_Today,DAY)

VAR _BaseAssessment = LOOKUPVALUE('Assessment Code'[Assessment Code Detail],'Assessment Code'[Assessment Code], 600)
VAR _Assessment_1 =
    CALCULATE(

Hi Jose:
Thank you again for the fast response time. I’m going to take some time to digest the DAX you wrote. I was thinking we could have a functional summary table where if the LAST ASSESSMENT = CURRENTLY NEXT SCHEDULED ASSESSMENT we could add 1 to the codes (306 is exactly three months more than 305) and based on this code number do a LOOKUPVALUE for the ASSESSMENT CODES table to get the full description tied into the next higher number(when needed).
I see the calculated columns appear to producing the correct results and this is awesome.
Thanks so much for helping out of these questions!

Best regards,
Bill

Whitewater100 · February 18, 2021, 3:34pm

Hi Jose:

Sorry to come back on this again. I put in your new calc column in Patients and it seems to work for the most part.

I added a cSPARSR.pbix (112.9 KB) couple overview measures for active count but those aren’t important to this question.

For example #383 (TRCB001) started in Jan 2020. Was seen regularly thru 9-22-20. This persons next scheduled reassessment would be the 15 month. The report shows Baseline as next assessment and doesn’t show status. When I scroll thru I see a number of ones like this.

I did add a total elapsed months to help figure out what the next planned assessment should land - directionally. I also updated the status table to show the days that would reflect Too Early, Within Window and OVERDUE. I suppose all else can be called Within 60 Day Grace Period.

I’m not understanding the Duration measure yet but can see what’s going on other than that.

The last ones to check on are:
Next Reassessment and Status and where there are blanks.

Thanks a lot for any other ideas you might have and have a nice day!

Best regards,

Bill Latest Sample PBIX attached

I believe the answer is very close just wanted to check in on the last details. Also if someone is clinical discharge it is fine to filter out. I added that filter.

I was trying to take the first character for next assessment due, turn it into a value and then bounce that off the total elapsed time as another way of determining the status.

Any other input is welcome

Whitewater100 · February 18, 2021, 4:45pm

Hi Jose:
Please see this file. It has assessment month No in calc column in Assessment table.

Thank you.

BillSPARSR.pbix (116.6 KB)

jbressan · February 18, 2021, 5:39pm

Hi @Whitewater100

I did some change,

Only two calculated columns Next Assessment Month and Patient Discharge
Measures: Next Assessment Due Date, Next Assessment and Last Assessment Month

SPARSR - jbressanV4.pbix (336.0 KB)

Whitewater100 · February 18, 2021, 7:02pm

Hi Jose:
Thank you again. I think if you can please look at page one you will see where my questions come from.

The patients now e.g. 571-579 are due for the sixth month reassessment on 3-15-21. The report earlier did say 6 month but changed to 9 month.

The original three measures(or columns) I’m hoping to get are:
Next Reassessment Due
Next Reassessment Due Date
Status.
The file we have now has a column in Assessment Codes that shows the corresponding month number for each subsequent reassessment. I was thinking the Elapsed Time(MO) measure would help with the status. This looks at total time since first assessment.EG if 9 month next due re-assesssment (numeric of 9) gets compared to the Elapsed Time to determine the status. (Just one idea). If ELAPSED TIME (MO) = 11.1, then this one is “Overdue”

Anyways If you have the time and patience can you check it out in relation to page one?

I really appreciate the time and effort you have demonstrated.

Thank you!

Bill

sam.mckay · February 18, 2021, 10:07pm

Unbelievable support provided here. So impressed by your skills

jbressan · February 18, 2021, 10:55pm

Hi @Whitewater100

I’m hope this is the final solution

I updated the Calculated Column Next Assessment Month

Next Assessment Month = 
VAR _FirstDate = [Baseline Assessment Date]
VAR _LastAssesmentMonth = [Last Assessment Month]
VAR _Today = TODAY()
VAR _Days = DATEDIFF(_FirstDate,_Today,DAY)

VAR _BaseAssessmentMonth = LOOKUPVALUE('Assessment Code'[Months],'Assessment Code'[Assessment Code], 600)
VAR _Assessment_Init =
    CALCULATE(
        SELECTEDVALUE('Assessment Code'[Months]),
        FILTER('Assessment Code',
        _Days >= 'Assessment Code'[Open Window] && 
        _Days <= 'Assessment Code'[Close Window])
        )
VAR _AssessmentMonth = IF(_Assessment_Init = BLANK(), 
                        _BaseAssessmentMonth,
                        IF(_BaseAssessmentMonth > _Assessment_Init, _BaseAssessmentMonth, _Assessment_Init))

VAR _CheckLastAssessment = IF(_LastAssesmentMonth< _AssessmentMonth,TRUE(),FALSE())

VAR _CurrentMonth = 
    SWITCH(TRUE(),    
        //1 Condition
        _LastAssesmentMonth < _AssessmentMonth, _AssessmentMonth,
        //2 Condition
        _LastAssesmentMonth = _AssessmentMonth,
        VAR _NextAssessmentMonth =     
            CALCULATE(MIN('Assessment Code'[Months]),
            FILTER('Assessment Code', 'Assessment Code'[Months] > _AssessmentMonth ))    
            Return _NextAssessmentMonth,
        //3 Condition             
        _LastAssesmentMonth > _AssessmentMonth, _LastAssesmentMonth)

VAR _ValidMonth = IF(_LastAssesmentMonth = _CurrentMonth,TRUE(),FALSE())

VAR _NextAssessmentMonth =     
        CALCULATE(MIN('Assessment Code'[Months]),
        FILTER('Assessment Code', 'Assessment Code'[Months] > _CurrentMonth))

VAR _CurrentAssessmentMonth = IF(_ValidMonth,_NextAssessmentMonth,_CurrentMonth)

Return IF(Pateints[Patient Discharge],BLANK(),_CurrentAssessmentMonth)

SPARSR - jbressanV5.pbix (339.8 KB)

Whitewater100 · February 19, 2021, 1:24am

Hi Jose:

Thanks so much for your effort and stick-to nature you have demonstrated. I’m going to take this as a final solution and I’m sure everything works great. I’m going to digest this in the am in W.MA USA.

I really appreciate it.

Best regards,

Bill