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
mpmsltd
Helper I
Helper I

Daily Occupancy Average

Hi everybody,

 

I have a table that shows the Start Date, End Date and Customer ID. What I want to do is to see the daily occupancy average per period of time (year for example and be able to drill down to a month). Another requirement is the ability to see on each day how many occupants there were exactly (not sure if that would be on a different visual). 

For example:

for 11/07/2018- I would have everybody that started on this date minus people that left on this day plus people that have arrived earlier but either checked out later or haven't checked out yet (end date = blank).

This is pretty much a sample of what I have where num is the customer ID. Like I mentioned before the blank end date should be considered as not ended yet.

Can somebody please help me out? Thank you so much in advance!

 

numstart dateend date
70012/17/20158/11/2016 8:18
109612/31/201512/31/2015 21:27
109512/30/201512/30/2015 16:59
92012/15/201512/15/2015 15:31
108012/14/201512/14/2015 4:39
107712/9/201512/9/2015 13:21
107812/9/201512/9/2015 2:52
9048/26/20158/26/2015 12:01
89712/6/201512/6/2015 4:44
107412/2/201512/2/2015 3:08
1 ACCEPTED SOLUTION

Hi,

You may download my solution file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

19 REPLIES 19

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.