Count Distinct items (Leads) based on when they were owned, during a particular date range.

[srusch]

This is a follow up to this question:  Calculate how many leads owned (based on a start/end date) in a dynamic range of dates.  

I have a PBIx set up here :

As stated in my earlier question, I need to be able to count unique leads owned in any given time frame.  

The measure:  

# OWNED IN MONTH =

CALCULATE (

COUNTX (

FILTER (

LEADS,

LEADS[Start Date] <= MAX ( DATES[Date] )

&& (

ISBLANK ( LEADS[End Date] )

|| LEADS[End Date] > MAX ( DATES[Date] )

)

),

( [# LEADS] )

),

CROSSFILTER ( LEADS[Start Date], DATES[Date], NONE )

)

is close to working, but it's not distinct counting when I select a month from the filter.  It's very close, May 2020 should be 43 distinct, and any month selected beyond June is returning all rows.

I would also like to use the TIMEFRAMES data set to filter by, using Timeframe and Selected to filter the dates table.  But I'm 

[Ashish_Mathur]

Hi,

I think the correct way to solve this is to split the Leads dataset into multiple rows (one for each date).  This way we will need only the single date column (we will be able to do away with Start Date and End Date columns).

What's your opinion?

[srusch]

The real dataset is around 500,000 rows, I don't think that's feasible.  

[mahoneypat]

I looked at your pbix file.  When I go to Data View on your Leads table and put in your filter criteria on the two date columns, I see 42 filtered rows.  I think your measure is correct. 

Two other comments:

1. Should Leads that have an end date in the selected month be counted?  If so, you can change to > MIN instead of >MAX.

2.  You can use your Timeframes table w/o making any relationship.  Just use two variables to harvest the value of your start and end dates, and then use those variables in place of the MAX(Dates[Date]) in your measure.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[srusch]

Thank you, your 2. solution works for me!

[srusch · Accepted solution]

Here is the measure I ended up using:

# Owned In Timeframe FINAL =

VAR

MinDate = SELECTEDVALUE(TIMEFRAMES[StartDate],0)

VAR

MaxDate = SELECTEDVALUE(TIMEFRAMES[EndDate],0)

RETURN

CALCULATE (DISTINCTCOUNT(LEADS[Lead ID]),LEADS[Start Date] <= MaxDate, (ISBLANK(LEADS[End Date]) || LEADS[End Date] >= MinDate))

It seems to be counting appropriately.