Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
FlankyPank2___
Frequent Visitor

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 

             

 

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@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

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

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.