Forum Discussion

FlankyPank2___'s avatar
3 years ago
Solved

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

I'm new. Sorry!

 

I have lots of client data records:

'Data'

Person IDNameStageStart DateEnd DateGroup
x38SimPhase101/05/202231/09/2023Local
x39DomIntro03/05/202231/04/2023Local
x40ChloePhase113/11/202215/01/2023Local
x41EmmaPhase215/08/202225/09/2022Local
x42FuryIntro01/04/202230/06/2022Out 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 

             

 

 

3 Replies