Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Pre & Post Analysis

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,a...
Need Help
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    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.

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    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.

lbendlin's avatar
lbendlin
Super User
4 years ago

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

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Community Support
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        v-chenwuz-msft
        Community Support
        4 years ago

        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.