Forum Discussion
Need Help with aggregating schedule appointment data
shris1993 , Refer to the file attached after the signature Active/Current Employee in HR is solved. In the same way you can solve this one
Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZUHi amitchandak , Thank you so much for the reference. I am sure it will be a big help with my solution.
Hi shris1993
Questions:
1. so you counting Appointment ID active on certain date? Appointment ID is always unique value in the that column or could be same value twice and more?
2. active Appointment ID for certain date is like
Date =>Start Date and Date <=End Date
3. If 2 is yes, then DAY Diff is not relevant at all for your calculation? also no other criteria relevant for this counting?
4. your format date is dd/mm/yyyy or different one?
5. provide sample data with expected output
Hi some_bih ,
1. Appointment ID is always unique value in the that column
2.
active Appointment ID for certain date is like
Date =>Start Date and Date <=End Date - Yes this is correct , I get that Day diff is not relevant , I just put it to explain that i want to count that specific appointment as active for all the date between the range.
3. My format for date column in date dimension table as well as Start date and end date in fact table is mm/dd/yyyy
4. Based on your 2nd point , I tried to create a measure that counts the total active appointment for each date in Date dimension column , however a problem which i am facing is how to connect the relationship between Date column of date dimension table to both start date and end date column of the Fact table . If you can shed some light on that , that will also be helpful or there is any other way to do that.
5. This is my output I am looking for. The Date column here is coming from my Dimension Date Table.
Hi shris1993 you can create Date table based on CALENDARAUTO function, check CALENDARAUTO function (DAX) - DAX | Microsoft Learn
After that create Date table as required in Power BI (mark as Date table) and after that create measure as you wish
Hi some_bih Yes , Thankyou , I created the date dimension table , but I am facing problem in connecting it with both the start date and end date in my fact table.
shris1993 , Refer to the file attached after the signature Active/Current Employee in HR is solved. In the same way you can solve this one
Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU