Need help aggregating a provider schedule data

[shris1993]

Hi Guys,

I have a dataset of providers appointment schedule which looks something like the below table

I have the provider name column , then start date time and end date time for the appointment holds.
I have extracted the time from start date and end date column in two other columns which are start time and end time.
Then I have the reason type.

For my analytical reporting , my team wants to see the data in the below form, 
the table will have a date column (which i will get it from a date dimension table)
the date column will include dates from 2022 start till now and going forward , for each date they want to see how much time was blocked for each provider in a day.

The problem i am facing is in my fact table , I have start date and end date for the appointment , so if the same hold(appointment) repeats for 1 year starting (for example someone blocked 10 am to 11 am every day of the working days, starting from 01-01-2023 and it ends on 05-05-2024) from 2023 and going till 2024 , how do i connect that to the my date dimension table.

Can you please help me with the query.
I want the appointment in the following format.

Date	Provider name	aapointment hour	type
1/1/2024	gongalez	3	out of office
1/2/2024	gongalez	3	out of office
1/3/2024	gongalez	3	out of office
1/4/2024	gongalez	3	out of office
1/5/2024	gongalez	2	initial councelling
1/6/2024	gongalez	2	initial councelling
1/7/2024	gongalez	2	initial councelling
1/8/2024	gongalez	2	initial councelling
1/9/2024	gongalez	2	initial councelling

[shris1993]

Hi @dufoq3 , Sorry , I know it's a bit complicated.
I will try to explain it clearly.

I have a dataset that i have transformed in the power quesry and it looks like below.

It has a start date column , an end date column , and the days difference between them.

Now as you can see here in the table the days difference between the start date and end date can range from 100 days to 365 days or more. It just mean that this appointment is on a repeat basis for every working day for a year or 3 month or 6 month.(depends on the range difference between start date & end date).

lets take one line item from my fact table with starting date 02-02-2023 and end date 03-03-2024. so the days difference between them is a year. 
Now , between these date difference from end date to start date , whatever dates comes in between , they should count this appointment for those dates. (This is the step i am stuck at, how to connect this days difference range to a date dimesnion table so that whatever dates comes in between of those range , will count this appointmnet as 1)

I have created a date  dimension table in my model which ranges from the earliest date of start date column till the latest date of end date column.
I want to use it to filter and aggregate my fact table in the below output

Is there any way i can connect or query, so that when i drag and drop my date dimension table in the rows , it will show appointment count on that day by counting the fact table line item.

I hope you understand my question , let me know if you want any more clarification.

[dufoq3]

Hi @shris1993, I'd like to help you, but to be honest I don't know what exactly you need. Could you create sample data and then expectet result based on that sample data plase? Also try to describe it once again please 😉