Forum Discussion

rfickes's avatar
rfickes
Frequent Visitor
9 years ago

Calculating Census by Month / Week using ID, Admit Date, and Discharge Date

I have three columns of data: client_id, admit_date, and discharge_date. From this, I need to be able to calculate how many clients are in the program on any given day and be able to find average daily census numbers for a time period (week and month).

 

It seems to me that I should be able to do a form of COUNTIF of distinct client_id if the start_date is <= last day of the reporting period and the discharge date is >= first day of the reporting period, but I have no idea how to actually do that in a calculated measure.

 

Any help guiding me toward the right path would be greatly appreciated.

Need Help

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi rfickes,

     

    Can you please share some sample data to test?

     

    Regards,

    Xiaoxin Sheng

    • rfickes's avatar
      rfickes
      Frequent Visitor
      9 years ago

      Anonymous

       

      I'm not entirely sure how to do that, but the data is pretty simple.

       

      client_idadmit_datedischarge_date
      1 2016-10-21 2017-04-17
      2 2016-10-27 2017-03-26
      3 2016-11-01 2017-03-09
      4 2016-11-02 2017-03-14
      5 2016-11-15 2017-05-06
      6 2016-12-01 2017-04-10
      7 2016-12-02 2017-04-10
      8 2016-12-04 2017-05-26
      9 2016-02-13 2017-05-06
      10 2016-12-08 2017-05-17
      11 2016-12-14 2017-03-28
      12 2016-12-16 2017-03-09
      13 2016-12-16 2017-03-18
      14 2016-12-18 2017-03-30
      15 2016-12-19 2017-03-05
      16 2016-12-22 2017-05-08
      17 2016-12-30 2017-04-21
      18 2017-01-01 2017-03-07
      19 2017-01-04 2017-05-18
      20 2017-01-09 2017-03-29

       

      ... and so on for a couple thousand rows.

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Hi rfickes,

         

        According to your description, you want to get the distinct client count of selected date range, right?

        If this is a case, you can refer to below formula to calculate the distinct count of match client count.

         

        Steps:

        1. Create a calendar with original date.

        Calendar = CALENDAR(FIRSTDATE(Sheet2[admit_date]),LASTDATE(Sheet2[discharge_date]))

         

        2. Add a measure to original table to calculate based on select range on calendar table.

        Count = CALCULATE(DISTINCTCOUNT(Sheet2[client_id]),FILTER(ALL(Sheet2),[admit_date]>=FIRSTDATE(ALLSELECTED('Calendar'[Date]))&&[discharge_date]<=LASTDATE(ALLSELECTED('Calendar'[Date]))))

         

        3. Use calendar date as the source of slicer, then calculate with selected date.

         

        Regards,

        Xiaoxin Sheng

  • Iadem's avatar
    Iadem
    Frequent Visitor
    9 years ago

    i think you need something like the measure
    =CALCULATE(SUM([id]);DATESBETWEEN('Таблица1'[date 2];FIRSTDATE('Таблица1'[date 2]);LASTDATE('Таблица1'[date 2])))

    Or you can use columns (=weeks(); and =month()), add them to slicer and use the measure AVG

    Sorry for my English ;)