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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.