Forum Discussion

Funkmiester's avatar
Funkmiester
Advocate I
8 years ago

census

I have posted this in the past and have made limited progress, I have also been away from this for about 6 months and am now back to needing it! It is specifically to count the number of patients in ...
Need Help
Vvelarde's avatar
Vvelarde
Community Champion
8 years ago

Funkmiester

 

Hi, lets try with this:

 

A New Table:

 

CalendarTable = CROSSJOIN(CALENDAR("01/01/2017","31/12/2017"),GENERATESERIES(0,23,1))

A measure:

 

Count-AttendanceID =
VAR Attendance =
    COUNTROWS (
        FILTER (
            ALLSELECTED ( Table1 ),
            Table1[Attendance Date] = SELECTEDVALUE ( CalendarTable[Date] )
                && Table1[Attendance Hour] <= SELECTEDVALUE ( CalendarTable[Hour] )
        )
    )
VAR Discharge =
    COUNTROWS (
        FILTER (
            ALLSELECTED ( Table1 ),
            Table1[Discharge Date] = SELECTEDVALUE ( CalendarTable[Date] )
                && Table1[Discharge Hour] <= SELECTEDVALUE ( CalendarTable[Hour] )
        )
    )
RETURN
    Attendance - Discharge

Let me know if works

 

Regards

 

Victor

Lima - Peru

Funkmiester's avatar
Funkmiester
Advocate I
8 years ago

Once again thank you very much Victor,

I've tried it and have some very odd answers, I understand it and it should work.

I am in the process of de-bugging it and am trying it piece by piece comparing the measure outputs of the admission and discharge numbers with the ACTUAL numbers which are easy to get form tables of (Admission date/Admission Hour+Patient ID) and (Discharge date/Discharge Hour+Patient ID). The trick is to subtract one from the other on an ongoing basis. 

At the moment they show different numbers and I need to find our why, however it does change based on the slicers.

I will keep trying on this and keep posting as I hope others will benefit once it's cracked.

 

Thanks, and any other options welcome!

Paul