Convert start/end date pair into an occupancy histogram

[transformasian]

Hello Community!

I have a start date and an end date pair for each kid and I want to turn this into a chart showing occupancy by month.

All kids have a start date.

Some kids are still in residence, so their end date is blank.

I was going to create a column for each month/year and then sum each column, but I'm thinking there must be a better way, not to mention I have to add a new column every month.

Advice? Tips? Links? Samples?

Thanks from a novice user

Allen

[v-piga-msft]

Hi  @transformasian,

Do you want to create a histogram visual like below?

I'm a little confused about your requirement, could you share your expected output, so that we can help further investigate on it?

Best Regards,

Cherry

[transformasian]

Hi Cherry,

Here are some sample rows:

PersonID	StartDate	EndDate
02440104	1/9/2017	8/2/2017
02631204	1/9/2017	5/25/2017
00200203	1/24/2017
02301306	2/9/2017	10/26/2017
02631204	5/25/2017	1/23/2018
02751104	8/30/2017	3/19/2018
02631105	10/12/2017	3/19/2018
00080907	11/27/2017	4/20/2018
00231303	2/12/2018	4/20/2018

and here's what I'd like to do - a histogram of headcount/occupancy by month:

Ideas? Suggestions?

[Ashish_Mathur]

Hi,

My solution here should get you started - Distribute projected revenue annually.

Hope this helps.

[transformasian]

Hello Ashish,

I am unable to download your solution - McAfee is blocking the site.

Can you post it here on the Power BI site somewhere?

Thanks.

[Ashish_Mathur]

Hi,

Download the solution workbook from here.

Hope this helps.