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

## Calculate how many leads owned (based on a start/end date) in a dynamic range of dates.

I need to count how many leads were owned by reps during any given time frame, 1 month, 2 months, 1 quarter, 2 years, etc.  I have an ownership start and end date in the Lead table with duplicate leadkeys since a lead could be owned during different time frames.

I can easily count how many were assigned out in any given time frame based on the start date, but if I want to count how many leads were owned during that same time frame, I must be about to filter out rows where the start date is after the MAX (or End Date) of the selected end date and where the EndDate is before the Selected Start Date (or MIN).

I have:

a Lead table with a start date and end date

a Calendar table with consecutive days

a Timeframe table that will function as a slicer so a user could select that they want to filter by Years and have the Years ( say 2010-2020) populate the slicer to select from.

In this example, The Time frame is 2020-H2, so the start date of the period is 2020.07.01 to 2020.12.31

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV

## Re: Calculate how many leads owned (based on a start/end date) in a dynamic range of dates.

@srusch ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

5 REPLIES 5
Highlighted
Microsoft

## Re: Calculate how many leads owned (based on a start/end date) in a dynamic range of dates.

Hi @srusch ,

Would you please inform us more detailed information( your  data(by OneDrive for Business)) if possible? Then we will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.

Best Regards,

Dedmon Dai

Highlighted
Super User IV

## Re: Calculate how many leads owned (based on a start/end date) in a dynamic range of dates.

@srusch ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper I

## Re: Calculate how many leads owned (based on a start/end date) in a dynamic range of dates.

Here's a link to a PBI I've set up with dummy data.  All dates and ids are arbitrary, but should make sense for this exercise.

My need is to be able to count leads assigned and leads owned during any given date range.  I will use a slicer to let users pick Years, HalfYears, Quarters, or Months.  Let's assume the timeframe will be May 2020.

Based on the start and and end date of May 2020 (2020.05.01 - 2020.05.31), how many leads did we own?

I can get the leads that were assigned, since I just need the lead ownership start date.  But to get leads owned, I need to be able to count the leads that were assigned during May and those assigned Before May that either Ended ownership in May or continue to be owned.

In the Leads Table, I input an ownership end date as 20200625 with the assumption that I will use GETDATE() to populate the End Date when it's NULL.

Highlighted
Helper I

## Re: Calculate how many leads owned (based on a start/end date) in a dynamic range of dates.

It looks like that HR Analytics solution works, thank you so much!

Highlighted
Helper I

## Re: Calculate how many leads owned (based on a start/end date) in a dynamic range of dates.

Well, it worked partially.  the Owned In time frame calculation from the HR Analyst example is close, but still need a way to count distinct lead IDs.

Here are the calculations I built:

# LEADS = DISTINCTCOUNTNOBLANK(LEADS[Lead ID])

# ASSIGNED IN MONTH = CALCULATE(DISTINCTCOUNTNOBLANK(LEADS[Lead ID]),USERELATIONSHIP(LEADS[Start Date],DATES[Date]))

# ENDED IN MONTH = CALCULATE([# LEADS],USERELATIONSHIP(LEADS[End Date],DATES[Date]),NOT(ISBLANK(LEADS[End Date])))

# 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))

## Helpful resources

Announcements

#### Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

#### Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors