count number of active sales people pr day based on start and dismissal day of employee ex. weekend

[Anonymous]

Hi all, 

I want to calculate the number of active sales people pr day between two dates (which can be sumed in the total - for budget calculation) however, I want to exclude weekends and public holidays. I have a table of all over agent which has a column with different start dates and a second column with dates of dismissal (for active employee the value is TODAY()).  I have all our public holidays listed in a table with dates. 

Can anyone help me calculate the number of active sales people pr day between two dates excluding weekends and holidays?

Best,

NicoM96

[v-xiaotang]

Hi @Anonymous 

Thanks for reaching out to us.

Could you provide

(1) a sample file, you can replace raw data with bogus data to protect your privacy,

(2) or provide some sample data that fully covers your issue/question

(3) give your expected result based on the sample you provide

Thanks.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , if you want to know active , does week end matter ?

countx(filter(Table, Table[Start]<= today() &&(isblank( Table[Dismissal Date]) || Table[Dismissal Date] >= Today()) , Table[Employee]) 

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

[Anonymous]

Hi again,

I need to calculate our sales budget for each day which is a determined from the number of active sales people. I want to create a table that shows the number of active people by date and all weekends should show a value of 0. The measure should be sumed in order to calculate the YTD budget