Dynamically calculate days of service column

[abisrani]

Hello Community,

I have a dashboard where I am trying to calculate the years of service for a salesperson, just like calculating age for someone as of the selected date. I have a date slicer on the dashboard and the years slicer, where a user can select date and also years from the slicers. 
 let us say someone selected the date and then there is another filter on the dashboard which has fields 1 year, 2 years, 3 years, 4 years, 5 years of service.  Now the start date and end date should work to calculate the days of service based on the selected field.

My Sales table has the data which consists of sales id , start date, and end date. I want to dynamically calculate a column based on the visual slicers above where the user will first select the date let us say today's date and 3 from the other slicer. This should calculate the days of service dynamically from sales table from the start date and the selected date in the slicer and should only return the values which is there for 3 years with us.

[v-shex-msft]

Hi @abisrani ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[DataNinja777 · Accepted solution]

Hi @abisrani 

Days of service can be calculated by creating a measure in a data model with two disconnected tables:

1. Sales rep table with the information of sales rep ID, start date, end date

2. Calendar table

You can for example, write a measure like below to flexiblty analyse the tenure information with respect to the time dimension:

The article in the link below shows technique of employee headcount and tenure which can be applied to salesperson year of service, too.

https://p3adaptive.com/finding-the-magic-part-2-on-the-way-to-data-happiness/

Best regards,