Need Help with aggregating schedule appointment data

[shris1993]

I have a fact table like below:

Every appointment has a start date and end date , and the days diff shows how long is it active, for example appointment 1 is active for more than a year with 394 days between start date and end date.
Now i want to see this data aggregated in the below format. (Basically i want to see every date starting from 2022 till 2024, and count of total appointment active on that date)
Foe eg. appointment number 8 is active for 365 days so for every date starting from 2/2/2023 till 2/2/2024 will include appointment 8 in their total active count of appointment.

I want the data to be shown as follow:

I have a date dimesion column.
which i want to use for the Date column in output table.

[amitchandak · Accepted solution]

@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-tr...
Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU

[some_bih]

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

[shris1993]

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.

[some_bih]

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

[shris1993]

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.

[amitchandak · Accepted solution]

@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-tr...
Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU

[shris1993 · Accepted solution]

Hi @amitchandak , Thank you so much for the reference. I am sure it will be a big help with my solution.