cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
analystict
Helper I
Helper I

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
AlB
Super User III
Super User III

 

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 )

 

View solution in original post

2 REPLIES 2
AlB
Super User III
Super User III

 

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 )

 

View solution in original post

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!

 

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.