Calculated column between two dates problem

[jcawley]

Good morning all,

I’m having a problem with a calculated column! My goal is to flag customers that had an appointment after being contacted sometime 90 days before the appointment up to the day of the appointment.

I’m using two tables.  The Appointments Table table where the calculated column is, and a Customer table that has contact records.

Appointment Table
Unique ID	Appointment Date	Desired output
123	9/2/2021	1
234	5/1/2021	1
345	2/1/2021	0
456	2/1/2021	0
123	5/1/2021	0

Customer Table
Unique ID	Call Date
123	9/1/2021
234	4/1/2021
345	9/1/2020
456	2/5/2021
123	12/1/2019
123	12/1/2018
123	12/1/2017

My calculated column looks like this so far:

Column 09022021 =

VAR APTDateMIN = Appointments Table[Appointment Date] - 90

VAR APTDate = Appointments Table[Appointment Date]

VAR APTPT = Appointments Table[Unique ID]

RETURN

    CALCULATE (

        DISTINCTCOUNT ( Appointments Table[Unique ID] ),

          FILTER(Customer Table,Customer Table[Unique ID]=APTPT

            && APTDateMIN<= Customer Table[Call Date]

            && Customer Table[Call Date] <= Appointments Table[Appointment Date]))

What happens when I use this is it only finds dates that = the exact date on both ends. It isn’t showing dates that occur in between the range. Only equals the exact date. Any help would be appreciated!

[v-kkf-msft]

Hi @jcawley ,

Please create an inactive relationship between Appointment and Customer table.

Then try the following formula:

Column 09022021 = 
VAR APTDateMIN = 'Appointment Table'[Appointment Date] - 90
VAR APTDate = 'Appointment Table'[Appointment Date]
VAR APTPT = 'Appointment Table'[Unique ID]
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Appointment Table'[Unique ID] ),
        FILTER (
            'Customer Table',
            'Customer Table'[Unique ID] = APTPT
                && APTDateMIN <= 'Customer Table'[Call Date]
                && 'Customer Table'[Call Date] <= 'Appointment Table'[Appointment Date]
        ),
        USERELATIONSHIP ( 'Appointment Table'[Unique ID], 'Customer Table'[Unique ID] )
    )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[jcawley]

Good morning Wizards,

I’m having a problem with a calculated column! The problem is trying to filter dates on a related table that are between 90 days before an appointment, and the date of the appointment.

I’m using two tables. The main table that has appointment data where the calculated column is, and a table that has contact records regarding customer data.

My calculated column looks like this so far:

Measure 083121 =

VAR APTDateMIN = ‘Appointments’[Appt Date] - 90

VAR APTDate = ‘Appointments’[Appt Date]

VAR APTID = ‘Appointments’[Unique Customer ID]

RETURN

    CALCULATE (

        DISTINCTCOUNT ( 'Appointments'[Unique Customer ID] ),

        ‘Appointments’[Saved Status] <> BLANK ()

            && ‘Appointments’[Saved Status] <> "Active"

            && ‘Appointments’[Saved Status] <> "New",

        Contact Record Table[Unique Customer ID] = APTID

            && Contact Record Table[status] <> "Deceased"

            && Contact Record Table[status] <> "Unreachable",

        FILTER (

            Contact Record Table,

            AND (

                APTDateMIN <= Contact Record Table[contact date],

                Contact Record Table[contact date] <= APTDate

            )

        )

    )

What happens when I use this is it only finds dates that = the exact date on both ends. It isn’t showing dates that occur in between the range. Only equals the exact date. If I remove the = signs nothing populates! Any help would be appreciated.

[jcawley]

Attempting again in another thread. Added tables this time and took some data out to simplify problem. 

https://community.powerbi.com/t5/Desktop/Calculated-column-between-two-dates-problem/m-p/2054705#M76...

[DataInsights · Accepted solution]

@jcawley,

Try this solution.

1. Create a table with distinct customers. Calculated table:

CustomerMaster = 
DISTINCT (
    UNION (
        DISTINCT ( Appointment[Unique ID] ),
        DISTINCT ( CustomerCall[Unique ID] )
    )
)

2. Create relationships:

3. Create calculated column in Appointment table:

Flag = 
VAR vAptDate = Appointment[Appointment Date]
VAR vAptDateMin = Appointment[Appointment Date] - 90
VAR vRowCount =
    CALCULATE (
        COUNTROWS ( RELATEDTABLE ( CustomerCall ) ),
        CustomerCall[Call Date] > vAptDateMin,
        CustomerCall[Call Date] <= vAptDate
    )
VAR vResult =
    IF ( vRowCount > 0, 1, 0 )
RETURN
vResult