Pre & Post Analysis

[Anonymous]

Hello All,

I am trying to count the number of calls before and after a particular date: I have the following tables
All calls Table(It has dates(from 2018 to Date), Incident numbers,patient names,age etc)

Patient Table(It has patient names(ABOUT 61), Date of enrollment into a program(September 15, 2021, same for all 61 patients), and DOB)

I have linked the two tables together in the data model.

End Goal: I am trying to get a count of calls 6 months before and after the date of enrollment for the patients in the second table relative to the first table

Second Goal: Is to get a percentage count of the patient table to the general call volume in the Call Table.

Any Ideas are welcome. Thank You. Cannot share the main data due to the sensitive nature

[v-chenwuz-msft · Accepted solution]

Hi @Anonymous ,

6 months before and after date of enrollment.

You need a table contains a column which used to be the Axis like line chart you provided. I create a table like this:

Then create a measure via this code:

Measure =
VAR _d =
    CALCULATE (
        SELECTEDVALUE ( Patients[Date of Review] ),
        ALLNOBLANKROW ( Patients )
    )
VAR _diff =
    SELECTEDVALUE ( 'Axis'[value] )
VAR _datestart =
    DATE ( YEAR ( _d ), MONTH ( _d ) + _diff, 1 )
VAR _dateend =
    DATE ( YEAR ( _d ), MONTH ( _d ) + _diff + 1, 1 ) - 1
RETURN
    IF (
        SELECTEDVALUE ( Patients[Full Name] ) = BLANK (),
        COUNTROWS (
            FILTER (
                ALL ( 'Calls' ),
                [Incident Date Time] >= _datestart
                    && [Incident Date Time] <= _dateend
            )
        ),
        COUNTROWS (
            FILTER (
                'Calls',
                [Incident Date Time] >= _datestart
                    && [Incident Date Time] <= _dateend
            )
        )
    )

Before you use this measure correctly, a relationship between calls and patients on name need to be created.

And result:

Best Regards!

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@lbendlin , Many thanks for your readiness to help. I am attaching the tables and expected results to this post. Feel free to ask questions about the data you might have:

Incident Number	Date	Age	Disposition	Description	Location	ZIP	Patient Name
G210056010	1/1/2018	14	Sick	Cant walk	Accra	23345	Baba,Kosi
G210056011	1/2/2018	15	Pain	sleep walk	Nairobi	23346	Melic,Konan
G210056012	1/3/2018	16	neck issue	red stool	Capetown	23347	Babe,Roon
G210056013	1/4/2018	17	scratches	diarhhea	Lusaka	23348	Musa,Traore
G210056014	1/5/2018	18	itches	cough	Brazzaville	23349	Tonga,Managa
G210056015	1/6/2018	19	COPD	Cant walk	Accra	23350	Haya,Wakubo

Full Name	DOB	Date of Review
Baba,Kosi	8/16/1948	9/15/2021
Melic,Konan	5/31/1985	9/15/2021
Tonga,Managa	2/19/1981	9/15/2021

[lbendlin]

You say that date of enrollment is September 15, 2021 for all.  Your sample incident data only has calls before that date, and none of the calls are within six months.  

Without sample data that matches the request it is hard to come up with a meaningful proposal.

[Anonymous]

@lbendlin The incident data is such a huge one it will be tedious to mask the names and may not attach them as a file. What I was hoping is that we could use the sample to come up with a solution. What do you recommend??

[lbendlin]

If you like to get meaningful help you will want to provide meaningful sample data that fully covers your issue.

[Anonymous]

https://docs.google.com/spreadsheets/d/1T5yKwb0SDkwOzSsHukkekj5IcK4SAte9/edit?usp=sharing&ouid=10606... 

I have obtained a year worth of data. We can try 5 months before and maybe 3 months after. Let me know if the link works .

Thanks for your help

[v-chenwuz-msft · Accepted solution]

Hi @Anonymous ,

6 months before and after date of enrollment.

You need a table contains a column which used to be the Axis like line chart you provided. I create a table like this:

Then create a measure via this code:

Measure =
VAR _d =
    CALCULATE (
        SELECTEDVALUE ( Patients[Date of Review] ),
        ALLNOBLANKROW ( Patients )
    )
VAR _diff =
    SELECTEDVALUE ( 'Axis'[value] )
VAR _datestart =
    DATE ( YEAR ( _d ), MONTH ( _d ) + _diff, 1 )
VAR _dateend =
    DATE ( YEAR ( _d ), MONTH ( _d ) + _diff + 1, 1 ) - 1
RETURN
    IF (
        SELECTEDVALUE ( Patients[Full Name] ) = BLANK (),
        COUNTROWS (
            FILTER (
                ALL ( 'Calls' ),
                [Incident Date Time] >= _datestart
                    && [Incident Date Time] <= _dateend
            )
        ),
        COUNTROWS (
            FILTER (
                'Calls',
                [Incident Date Time] >= _datestart
                    && [Incident Date Time] <= _dateend
            )
        )
    )

Before you use this measure correctly, a relationship between calls and patients on name need to be created.

And result:

Best Regards!

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-chenwuz-msft Many thanks for your suggestion solution and Dax. I will test it out and give you feedback soon.

I have two questions though:
1. Is there a way to arrange the DimMonth to have the negatives(Pre) on the left and the positives(Post) on the right of the chart.

2. Can I modify the Dax to only look at 5 months before and 3 months after and finally
3. Can I use the same dax formula if the Date of review is different for each patient? Please advise.

Thank You!!

[v-chenwuz-msft · Accepted solution]

Hi @Anonymous ,

Response of your questions.

2. The Axis table ( dimmonth ) was created by enter data, so just remove -6 month and others you do not want, but keep the [value] is right due to the [value] is identified as parameter for the DAX formula.

1. Sort the column(dimmonth) by [value] column.

3 If there are different review date for each patient, please use this code:

Measure =
VAR _diff =
    SELECTEDVALUE ( 'Axis'[value] )
VAR _s =
    SUMMARIZE (
        'Patients',
        Patients[Full Name],
        Patients[Date of Review],
        "COUNT",
            COUNTROWS (
                FILTER (
                    'Calls',
                    [Incident Date Time]
                        >= DATE ( YEAR ( EARLIER ( Patients[Date of Review] ) ), MONTH ( EARLIER ( Patients[Date of Review] ) ) + _diff, 1 )
                        && [Incident Date Time]
                            <= (
                                DATE ( YEAR ( EARLIER ( Patients[Date of Review] ) ), MONTH ( EARLIER ( Patients[Date of Review] ) ) + _diff + 1, 1 ) - 1
                            )
                )
            )
    )
RETURN
    SUMX ( _s, [COUNT] )

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-chenwuz-msft I want to thank you for the solution you provided. It was really on point.

If I want to add 1yr+ to the Axis table, will it work??

Please advise

[lbendlin]

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to work with. Avoid posting screenshots of your source data if possible.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[Anonymous]

Greetings Sir,

I am following up on the formula you help me with earlier this year.

The formula has worked well so far but I have a new scenario I want to try to add to the chart:

When I select a patient to view their pre-post calls, I noticed that some patients do not have any calls after 3 months so the chart ends on the 3-month post. However, I would like that chart to show either a flat line or indicate zero calls so readers of the chat will know that the patient has stopped calling after, let's say, the 3rd month.

 So far I have tried to increase the Axis with two additional months after the 6-Month but it doesn't seem to be showing a flat line but rather the looking downwards.

Any ideas to tweak the formula to achieve this just like the picture I share earlier with you??

Your help is greatly appreciated.

Regards