Counting Daily Occupancy using a Start and End Date and being able to apply filters?

[FlankyPank2___]

I'm new. Sorry!

I have lots of client data records:

'Data'

Person ID	Name	Stage	Start Date	End Date	Group
x38	Sim	Phase1	01/05/2022	31/09/2023	Local
x39	Dom	Intro	03/05/2022	31/04/2023	Local
x40	Chloe	Phase1	13/11/2022	15/01/2023	Local
x41	Emma	Phase2	15/08/2022	25/09/2022	Local
x42	Fury	Intro	01/04/2022	30/06/2022	Out of Area

My end aim is to have a chart which plots daily occupancy. 

I could have a seperate chart for each stage but would like to be able to responsively filter the group and even better be able to drill through to see which clients are within each daily count.

This is where i am so far! (Sorry again)

I created a new tabe for a calendar:

Calendar = CALENDAR(DATE,2022,4,1),DATE(2023,3,31))

Then I asked the calendar to count how many clients were captured within each to-From date:

Client Count =

CALCULATE ( 

   COUNTROWS ('Data') +0,

   FILTER (

       'Data',

       'Data'[Start Date] <= EARLIER ('Calendar'[Date] )

           && 'Data'[End Date] >= EARLIER ('Calendar'[Date] )

  )

)

This works fine as an overall count, but i'd really like to be able to filter the 'Stage' a client is at or the 'Group' they were from and get a count for that too.  Like a responsive count?  

I'm brand new to Power BI & i don't understand DAX yet, its a google search and copy process for me.  I'm not sure if a relationship is even possible between these two tables.

Thanks in advance 

[amitchandak · Accepted solution]

@FlankyPank2___ , Check if these approch can help

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...

Measure way
Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM

[FlankyPank2___]

Thank so much!! i've got something to work with now.  I followed the youTube tutorial using the measure and with a few tweaks to my data it enabled me to link the two tables and plot a daily occupancy.  I'm very chuffed. I've just got a bit of work to do now to make it look pretty.

Thanks again

[FlankyPank2___]

Thank you for taking the time to find something that might help me 🙂  It looks promising, i'm going to attempt the Measure way initially.  Thanks again