Comparing multiple dates with multiple tables

[ajbogle]

Hi folks,

I've been working on a problem for about a day and can't seem to wrap my head around a working solution.

Problem: We use Dialpad which is a VOIP software for our Project Managers. I need to determine if a Call or Text happened between certain dates, so this will be used for about 6 different occurences. But for the sake of trying to solve this, we can stick to the first occurence which is our Welcome Call.

Operations Table

Start Date = Site Survey Approved

End Date = +7 days from the Approved Timestamp

Call Record Table

call_date_started = When the call took place

These two tables are joined together based off the phone number which is the primary key for the Call Record and FK for the Operations table. When they are joined, I can bring over the PK from the Operations table which is called [RID].

I'm trying to determine a Yes or No = Did the call happen within the Start Date and End Date (7 days)

[Mikelytics · Accepted solution]

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.

[ajbogle]

Brilliant! This work perfectly, I had the seperate Phone table but I never thought to reference both the Ops/Call record table.

Cheers!

[Mikelytics]

Hi @ajbogle ,

Awesome and thanky you for your feedback! 🙂 This is one of the gret benefits of using a starschema model (Understand star schema and the importance for Power BI - Power BI | Microsoft Learn). Your oeprations and record tables are fact tables and the phone table is a dimension tables. You can connect multiple fact tables to your dimension tables using a key which enebales you to do cross-tables calculation for this key. It is also common to have multiple dimensions tables connectiing to mutliple fact tables which enables more detailed cross-table analysis.

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.

[AllisonKennedy]

@ajbogle  I'm not sure how Phone number is a PK, but if that's the case, you can work in the Operations table and add a new column using the RELATED function. Something like:

Welcome Call In Dates = 

IF( RELATED(CallRecord[call_date_started]) >= Operations[approvedtimestamp] && RELATED(CallRecord[call_date_started]) <= Operations[approvedtimestamp] + 7,

"Yes",

"No"

)