Dealing With Date Ranges Separate from my Date Table

[jarwest]

Hey all,

I've run into a small problem with one of my reports. The data denotes when a kid has in school suspension, for how many days, and the reason. The end result has each day as a row, with the number of kids for each reason present. The end result should look something like this table below.

Date	Tardiness	Fighting	Bullying
8/2/2024	2	0	2
8/3/2024	1	2	1
8/4/2024	2	2	0
8/5/2024	2	1	1
8/6/2024	1	0	1

I get the underlying data by kid and the number of days then have detention. I have 2 tables, one of which is a regular Date table created using CALENDAR() and from a table containing the data by child. The data I have looks something like this table below.

Name	StartDate	EndDate	Reason
Jimmy	8/1/2024	8/7/2024	Tardiness
Tommy	8/4/2024	8/5/2024	Tardiness
Rosie	8/3/2024	8/5/2024	Fighting
Julie	8/3/2024	8/4/2024	Fighting
Bobby	8/1/2024	8/2/2024	Tardiness
Jerry	8/1/2024	8/2/2024	Bullying
Mikey	8/1/2024	8/3/2024	Bullying
Evie	8/5/2024	8/11/2024	Bullying

So for example, Tommy has suspension on 8/4/2024 and 8/5/2024, so the suspensions for Tardiness go up by 1 on those days. The only other kid in suspension for Tardiness those days is Jimmy, so there's a 2 for those 2 dates.

My date table looks something like this, just a straight column with dates

Date

8/2/2024

8/3/2024

8/4/2024

8/5/2024

8/6/2024

I need to find a way to join the date table and suspension table so that each row is a date like the table up top, and so that the counts per suspension match up.

Let me know if more detail is needed regarding this issue. Any and all help is appreciated!

[lbendlin · Accepted solution]

I need to find a way to join the date table and suspension table

Not really.  Leave them disconnected. Use a measure to calculate the intersection.