Getting the number of sents based on leave days

[Anonymous]

Hi all,

We have a pulse survey we use in our organisation which we send to every employee in the organisation on a weekly basis.

The survey is sent on every wednesday and to get the number of sents, we used to count the headcount in the organisation on each wednesday and the url of the survey is always the same (breaks down the results based on the response date), but this calculation is not correct because; let's say I am on annual leave from August 11 to August 20. That means, I am not able to respond to pulse survey for W33, therefore, I should be excluded from the number of sents for W33.

However, I am not sure how I can make this calculation, seems quite complex to me.

Any workaround that you can think about?

Best regards,

Ugur

[amitchandak]

@Anonymous , Can you share sample data and sample output in a table format?

This file lets you know how to exclude few days from date diff. Refer 2nd Page

https://www.dropbox.com/s/y47ah38sr157l7t/Order_delivery_date_diff.pbix?dl=0

[Fowmy]

@Anonymous 

Can you share some sample data and the expected result to have a clear understanding of your question?
You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.
____________________________________
How to paste sample data with your question?
How to get your questions answered quickly?

_____________________________________
Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[Anonymous]

Hi @Fowmy @amitchandak,

Thank you for your quick responses.

Here you can find the table:

https://drive.google.com/file/d/1iFQ739cG0GDYv1Epj2ONF5UAM5U5Ivwm

There are two tables in two seperate sheets:

1- Employee table through which we count the number of sents by counting the number of active people in the organisation on every wednesday (in this example it's week 32, August 5th has been taken as example).

2- Absence table where we see the absence dates per employee. If you look at for example "Employee ID 396", he has been off btw August 5-12 therefore there's no chance that the can fill in the survey for W32, therefore, he should be dropped from the number of total sents.

I hope this clarifies for you.

Best regards,

Ugur

[Fowmy]

@Anonymous 

Can you share the current measure that you use to do the count and what is the relationship between both the tables?

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[Anonymous]

Hi @Fowmy ,

Here is the measure that I use for counting:

Total sent = DISTINCTCOUNT('Employee table'[Attribute:employeeId])*DISTINCTCOUNT('Employee table'[Year-Week])

There's an employee ID column in the Employee table which I removed from the excel I shared.

Currently, there's no direct relationship btw these two tables, since we didn't include the absences while calculating the total sents until now.

I have also a date table available in my report, therefore I am open to suggestions to change the report structure based on the needs if there's any healthier way to get the output.

Best regards,

Ugur

[v-lili6-msft]

hi  @Anonymous 

Use the date table as a bridge to create a relationship between 'Employee table' and 'Absences  table'

https://radacad.com/do-you-need-a-date-dimension

Regards,

Lin