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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

Getting the number of sents based on leave days

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

 

 

 

 

6 REPLIES 6
amitchandak
Super User
Super User

@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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
Fowmy
Super User
Super User

@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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

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

@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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

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

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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