Pre & Post Analysis

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.

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.