Forum Discussion

titus's avatar
titus
Icon for Helper I rankHelper I
9 years ago
Solved

calculations with user's date

Hello,

i have a table with 3 columns: name; admission date and discharge date. 

My objective is to calculate the total stay in days at my hospital according to the choosen month.

 

 

For instance, supposing a user chooses April. I should get:

- 20 days from Peter (21/04/2017 - 01/04/2017)

- 25 days from Mary (26/04/2017-01/04/2017)

But if my user chooses March, then i should get:

- 30 days from Peter (31/03/2017 - 01/03/2017) because he stayed at the hospital the full month (admission<Min Date && discharge>Max Date)

- 0 days from Mary, because her admission is > than de MAX Date(31/03/2017)

I've been around this problem over and over and i just can't reach a solution.

Thks in advanced,

Titus

Need Help
  • titus's avatar
    titus
    9 years ago

    Sucess!

     scottsen, thanks to your suggestion i've reach the solution:

     

     SUMX(Admissions;IF(AND(Admissions[Admission]<MIN(Calendar[CAL_DATE]);OR(Admissions[Discharged]>MAX(Calendar[CAL_DATE]);ISBLANK(Admissions[Discharged])));INT(MAX(Calendar[CAL_DATE])-MIN(Calendar[CAL_DATE])+1);0))

     

    this is one of the variant of the problem:

    suppose user's month is april. This formula is for those patients which had an admission before april and the discharge date is later than april (or blank, meaning the patient hasn't been discharged yet).

     

    Thk U so much! That push was what i needed. :) :)

    Titus

    Titus

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Do you have a separate Calendar table?  You are almost certainly going to need that -- but maybe you have one... as how are you "choosing a month" ?

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    I would naively write something like:

     

    =SUMX(Admissions, CALCULATE(COUNTROWS(Calendar), FILTER(Calendar, Calendar[Date] >= Admissions[Admission] && Calendar[Date] <= Admissions[Discharged])))

     

    If you have a lot of data, that will probably perform badly, and you can come back and yell at me :)

     

     

    • titus's avatar
      titus
      Icon for Helper I rankHelper I
      9 years ago

      Hi scottsen,

      i've been there also :)

       

      with sumx Admissions, i'm not subtracting any dates (am i?)

       

      (supposing april was the user's choice) if a patient has his admission at april/02/2017 and his discharge at april/23/2017, the calculation must be 22 days. How can i achieve that result? 

       

      once again, thks

      • titus's avatar
        titus
        Icon for Helper I rankHelper I
        9 years ago

        Sucess!

         scottsen, thanks to your suggestion i've reach the solution:

         

         SUMX(Admissions;IF(AND(Admissions[Admission]<MIN(Calendar[CAL_DATE]);OR(Admissions[Discharged]>MAX(Calendar[CAL_DATE]);ISBLANK(Admissions[Discharged])));INT(MAX(Calendar[CAL_DATE])-MIN(Calendar[CAL_DATE])+1);0))

         

        this is one of the variant of the problem:

        suppose user's month is april. This formula is for those patients which had an admission before april and the discharge date is later than april (or blank, meaning the patient hasn't been discharged yet).

         

        Thk U so much! That push was what i needed. :) :)

        Titus

        Titus