cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
analystict Regular Visitor
Regular Visitor

Using a single date filter against two date columns in a measure

Hey there,

 

What I am trying to do is to measure the amount of sales that are generated from leads which were created in a specific timeframe AND converted in the specified timeframe (filtered by date from a date dimension).  

 

This way I can see how many sales belong to the specified timeframe's leads and how many sales are "late" sales.

 

Since using two active relationships at a time in the same tables is not possible, I tried the following workaround:

 

First, I have this measure which works perfectly fine and takes the date that the lead was converted (converted_on):

 

Conversions_WithLate =
    CALCULATE(
        COUNTA('Lead'[id]),
        USERELATIONSHIP(DateTable[Date], 'Lead'[converted_on])
    )
    
RETURN Total

 

Then what I did as an attempted workaround, is to create another measure like so:

Conversions_NoLate = 
    CALCULATE(
        [Conversions_WithLate]
        USERELATIONSHIP(DateTable[Date], 'Lead'[created_on])
    )

Unfortunately, this approach did not work and I'm quite stuck. How do I go about this?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Using a single date filter against two date columns in a measure

 

Hi @analystict

 

Please always show the structure of the tables in your data model and their relationships. Otherwise people trying to answer have to start making guesses. Share the sample data in text-tabular format so that it can be readily copied.

 

From what I can understand, you could try the following:

 

Measure =
VAR LeadsConvertedInPeriod =
    CALCULATETABLE (
        DISTINCT ( 'Lead'[id] ),
        USERELATIONSHIP ( DateTable[Date], 'Lead'[converted_on] )
    )
VAR LeadsCreatedInPeriod =
    CALCULATETABLE (
        DISTINCT ( 'Lead'[id] ),
        USERELATIONSHIP ( DateTable[Date], 'Lead'[created_on] )
    )
VAR LeadsCreatedAndConvertedInPeriod =
    INTERSECT ( LeadsCreatedInPeriod, LeadsConvertedInPeriod )
RETURN
    COUNTROWS ( LeadsCreatedAndConvertedInPeriod )

 

2 REPLIES 2
Highlighted
Super User
Super User

Re: Using a single date filter against two date columns in a measure

 

Hi @analystict

 

Please always show the structure of the tables in your data model and their relationships. Otherwise people trying to answer have to start making guesses. Share the sample data in text-tabular format so that it can be readily copied.

 

From what I can understand, you could try the following:

 

Measure =
VAR LeadsConvertedInPeriod =
    CALCULATETABLE (
        DISTINCT ( 'Lead'[id] ),
        USERELATIONSHIP ( DateTable[Date], 'Lead'[converted_on] )
    )
VAR LeadsCreatedInPeriod =
    CALCULATETABLE (
        DISTINCT ( 'Lead'[id] ),
        USERELATIONSHIP ( DateTable[Date], 'Lead'[created_on] )
    )
VAR LeadsCreatedAndConvertedInPeriod =
    INTERSECT ( LeadsCreatedInPeriod, LeadsConvertedInPeriod )
RETURN
    COUNTROWS ( LeadsCreatedAndConvertedInPeriod )

 

analystict Regular Visitor
Regular Visitor

Re: Using a single date filter against two date columns in a measure

Hey @AlB,

 

Sorry about that! I usually do show the structure - slipped out of my mind this time trying to make this as clear as possible Smiley Very Happy

 

This works great though - it is exactly what I needed. Thank you!