Forum Discussion

analystict's avatar
analystict
Helper I
7 years ago
Solved

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 ...
Need Help
  • AlB's avatar
    AlB
    7 years ago

     

    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 )

     

AlB's avatar
AlB
Community Champion
7 years ago

 

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's avatar
analystict
Helper I
7 years ago

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 :smileyvery-happy:

 

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