Forum Discussion

transformasian's avatar
transformasian
Regular Visitor
8 years ago

Convert start/end date pair into an occupancy histogram

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

5 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Icon for Resident Rockstar rankResident Rockstar
    8 years ago

    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's avatar
      transformasian
      Regular Visitor
      8 years ago

      Hi Cherry,

       

      Here are some sample rows:

      PersonIDStartDateEndDate
      024401041/9/20178/2/2017
      026312041/9/20175/25/2017
      002002031/24/2017 
      023013062/9/201710/26/2017
      026312045/25/20171/23/2018
      027511048/30/20173/19/2018
      0263110510/12/20173/19/2018
      0008090711/27/20174/20/2018
      002313032/12/20184/20/2018

       

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

       

      Ideas? Suggestions?