Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
srusch
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
amitchandak
Super User
Super User

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

View solution in original post

5 REPLIES 5
amitchandak
Super User
Super User

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

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

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))
 
 
v-deddai1-msft
Community Support
Community Support

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

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.