Forum Discussion
Need help aggregating a provider schedule data
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.