Comparing multiple dates with multiple tables

Hi ajbogle,

Base on the input you gave the following approach should to it. But since you say there are more requriements I do not know whether it will cover it end-2-end. 🙂

Please find below my example data:

Operations Table:

Record Table:

Please be aware that the column FYI is only for your information about which call is in time, before or after. It will not be used in the further calculation.

Number Table (Dimension):

Data Model:

Measure: 

Calls In Time = 

SUMX(
    VALUES(Dim_TelephoneNumber[PK_TelNumber]),

        var var_StartTime = CALCULATE(MAX(Operations[Start Date]))
        var var_EndTime = CALCULATE(MAX(Operations[End Date]))
        var var_CallTime = CALCULATE(MAX('Call Records'[call_date_started]))

        RETURN

        IF(var_CallTime >= var_StartTime && var_CallTime <= var_EndTime, 1,0)

)

Result:

with 3 calls in time. The SUMX makes sure that you have the right total.

Additional Comment:

When there are further attributes like call type or something else which change the granularity it might be nexessary to do adjustments in the measure. But absed on your input and the understanding I got from it this should do the job or lead you to the result. 🙂

Best regards

Michael

-----------------------------------------------------

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

@ me in replies or I'll lose your thread.