cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
diegomorales
Regular Visitor

Group tenure months and count the total

Hi All,

 

I'll explain the data first:

 

I have a data set with

- customer number (BPART)

- start date of tenure

- end date of tenure

 

In order to report the total count of customer for each month, I have a relationship of many to many through a bridge to a Date dimension

diegomorales_0-1637640841302.png

So for instance, if the customer tenure starts in "jan2020" and ends in "December2021", and i am reporting for the month of "March2020", then I can count that customer as active for that month, and so on.. 

 

I can calculate the tenure in months for each customer using dax, and when I change the DATE, then the tenure changes acordingly

 

 

tenure_month = DATEDIFF(ENDOFMONTH(BP_TENURE_STAGE_BRIDGE[BP_TENURE_START]),MAX(DIMDATE[DateFullName]),MONTH)

 

 

diegomorales_2-1637641193642.png

 

What I need to do is to group this "tenure month" and count the total customers for each group, using the following grouping

  • < 3 months 
  • 4-6 months
  • 7-12 months
  • 13-24 months
  • 25-60 months
  • +61 months

This has to be dinamyc, because when I change the reporting period (from the Date Dimension), the tenure for some customer may move from one bucket of tenure into the other one.

 

So it would have to look something like this:

 

diegomorales_3-1637642053986.png

 

I imagine that this has to be achieved by creating a virtual table that summarises the values, but I cant figure out how to do it.

 

Due to sensitive data I cant share the PBI file

 

Thank you very much, and let me know if any extra clarification is needed

 

2 REPLIES 2
amitchandak
Super User
Super User

@diegomorales , I have used these formula in HR with a Date table with some changes they should work in this case

 

Method 1

Employees = COUNT(Emp[Employee Id ])
Hire = CALCULATE([Employees], USERELATIONSHIP('Date'[Date], Emp[Start Date]))
Teminated = CALCULATE([Employees], USERELATIONSHIP('Date'[Date], Emp[End Date]), not (ISBLANK(Emp[End Date])))

Total Emp = CALCULATE( Countx(FILTER(Emp, Emp[Start Date] <= Max('Date'[Date]) && (ISBLANK(Emp[End Date]) || Emp[End Date] >Max('Date'[Date]))),[Employees]) , CROSSFILTER('Date'[Date],Emp[Start Date],None))

 

 

Method 2

Cumm Hire = CALCULATE([Hire], FILTER(ALL('Date') , 'Date'[Date] <= max('Date'[Date])))
Cumm Termination = CALCULATE([Teminated], FILTER(ALL('Date'), 'Date'[Date] <= Max('Date'[Date])))
Current Employee = [Cumm Hire] -[Cumm Termination]

 

 

Date table is joined with a start date as active and end date with inactive join

 

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 !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!

Hi Mate, this looks great, but unfortunately it doesnt answer my main pain point whihc is creating virtually these bucket of tenure. I need to be able to group them and count the total by group, and that total has to change along with the customer moving onto diff buckets when I change the date

Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 without aka link and time 768x460.jpg

Check it Out!

Watch Session 22 Ted's Dev Camp along with past sessions!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!