cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
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. 

 

PBI Example.png

 

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

 

see if this can help https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...



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!

View solution in original post

5 REPLIES 5
Highlighted
Microsoft
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 IX
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.

 

see if this can help https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...



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!

View solution in original post

Highlighted
Helper I
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. 

 

Leads Owned by Timeframe 

 

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
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
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
Community Conference

Power Platform Community Conference

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

Upcoming Events

Experience what’s next for Power BI

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

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

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

Top Solution Authors
Top Kudoed Authors