cancel
Showing results 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 IX

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

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 ,

Thanks for your understanding and support.

Best Regards,

Dedmon Dai

Highlighted
Super User IX

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

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:

# OWNED IN MONTH =
CALCULATE(
COUNTX(

Announcements

#### Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

#### Experience what’s next for Power BI

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

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021