Help on how to Generate These Measures

[TARUGOKING]

- My dataset consists of records from 2024 and 2025
- PatientID is unique (like SSN).
- I'm using a TABLE visuailization
- It has 2 slicers {YEAR] and [MONTH]. Can only pick 1 YEAR and 1 MONTH
- Depending on what was picked on the slicers, PatientAdmitCount should count all until that slicers value. (example if picked APR 2025, it should evaluate from 2024 until slicers value.

- How to create Meaures (1) and (2)?

Thank you.

----------------------------------

in SQL:

-- SAMPLE DATASET
SELECT * FROM TEST ORDER BY FullDate,PatientID;

-- ADMISSION COUNT PER PATIENT
SELECT PatientID,PatientName,SUM(AdmitCount) AS PatientAdmitCount
FROM TEST
WHERE FullDate < '04/30/2025'
GROUP BY PatientID,PatientName;

-- DESIRED REPORT OUTCOME
WITH CTE AS ( SELECT PatientID,SUM(AdmitCount) AS PatientAdmitCount
FROM TEST
WHERE FullDate < '04/30/2025'
GROUP BY PatientID)

SELECT FullDate,
SUM(CASE WHEN C.PatientID=T.PatientID AND C.PatientAdmitCount < 2 THEN 1 ELSE 0 END) AS [NewAdmissions],
SUM(CASE WHEN C.PatientID=T.PatientID AND C.PatientAdmitCount > 1 THEN 1 ELSE 0 END) AS [ReAdmissions],
SUM(T.AdmitCount) AS TotalAdmissions
FROM TEST AS T
INNER JOIN CTE AS C ON T.PatientID=C.PatientID
WHERE YEAR(T.FullDate)=2025 AND MONTH(T.FullDate)=4
GROUP BY FullDate;

***********  SAMPLE DATA *********

PatientID,AccountNumber,PatientName,AdmitCount,DateKey,FullDateString,FullDate,Year,MonthNumberOfYear,MonthNameShort,DayNumberOfWeek,DayNameOfWeek
PID20240627123333570,AN00001083880,BANANA,1,20240708,07/08/2024,7/8/2024,2024,7,JUL,2,MON
PID20241111142412453,AN00001095504,APPLE,1,20241121,11/21/2024,11/21/2024,2024,11,NOV,5,THU
PID20241111142412453,AN00001100973,APPLE,1,20250124,01/24/2025,1/24/2025,2025,1,JAN,6,FRI
PID20240627123333570,AN00001104942,BANANA,1,20250404,04/04/2025,4/4/2025,2025,4,APR,6,FRI
PID20250319090359770,AN00001106160,MELON,1,20250408,04/08/2025,4/8/2025,2025,4,APR,3,TUE
PID20241111142412453,AN00001107903,APPLE,1,20250415,04/15/2025,4/15/2025,2025,4,APR,3,TUE
PID20250210135615720,AN00001102805,ORANGE,1,20250415,04/15/2025,4/15/2025,2025,4,APR,3,TUE

[Ashish_Mathur]

Hi,

Difficult to understand the data that you have pasted.  Share the download link of an MS Excel file with the data in one tab and expected result in another tab.

[kpost]

I was able to create PatientAdmitCount, but I need more information on what "new Admissions" and "ReAdmissions" means.  Can you explain how those are calculated and what your expected values would be for a given selection?

Here's your solution so far.

Create a date table that is not connected to your Fact table containing patient data.

DateDim = CALENDAR(DATE(2024,1,1), DATE(2025,12,31))

In that table create these columns:

Month_Num = MONTH(DateDim[Date])

YEAR = YEAR(DateDim[Date])

Then add slicers for each column with 'Single Select' turned on to your page.

And here's the measure for PatientAdmitCount, such that the patient count will be the number of admits between 1/1/2024 and the end of your selected Year/Month.

PatientAdmitCount =

    var start_Date = DATE(2024,1,1)

    var year = selectedvalue(DateDim[YEAR])

    var month = selectedvalue(DateDim[Month_Num])

    var end_Date = DATE(year, month, DAY(EOMONTH(DATE(year, month, 1), 0)))

RETURN

CALCULATE(COALESCE(SUM('sample'[AdmitCount]), 0), 'sample'[FullDate] >= start_Date, 'sample'[FullDate] <= end_date)

 Attached is the .pbix file.

 

 

[TARUGOKING]

Thank you. Here's additional info:

Using the initial dataset, with slicer set for APR 2025, my (1) [TotalAdmissions] is just the sum of the DS.[AdmitCount] per day.

The [NewAdmission] and [ReAdmission] just evaluates if the admitted patient for the MONTH+YEAR is a new patient or an old patient. 

[NewAdmission] + [ReAdmission] = [TotalAdmissions]

So for [ReAdmission], lets take the APRIL 4 as an example.

BANANA was admitted on 4/4/25. He is considered a ReAdmission because he had a prior admission record on 7/8/2024 (total admission record >1). 

So for [NewAdmission], lets take the APRIL 4/8 as an example.

MELON was admitted on 4/8/25. He is considered a NewAdmission because he had no prior admission record (total admission record =1).

[kpost · Accepted solution]

Alright, here's your DAX for NewAdmission, ReAdmission and Total_Admissions

I was a little unsure how you wanted to handle the situation where the same patient comes in more than once on the same day, especially if that's the first time the patient has been seen.

So the way I did it, if a patient is seen for the first time on a certain day and they go in more than once on that day, ONE will count as an admission, and the rest will count as a re-admission.

Also, if the same patient is seen twice in one month, they both will count. (Potentially one admission and one readmission, or both readmission, or whatever the case may be).

First, you need to add an index column in Power Query (It's easy just select New column and at the top click Add Index Column).

NewAdmission =

VAR ranktable =

    ADDCOLUMNS (

        'sample',

        "first_Date", CALCULATE ( MIN ( 'sample'[FullDate] ), ALLEXCEPT ( 'sample', 'sample'[PatientID] ) ),

        "DayRank", RANKX (

            FILTER (

                'sample',

                'sample'[PatientID] = EARLIER ( 'sample'[PatientID] )

                    && 'sample'[FullDate] = EARLIER ( 'sample'[FullDate] )

            ),

            'sample'[Index],,

            ASC,

            Dense

        )

    )

VAR result =

    SUMX (

        FILTER (

            ranktable,

            'sample'[FullDate] = [first_Date]

            && [DayRank] = 1

        ),

        [AdmitCount]

    )

RETURN

    COALESCE(result, 0)

ReAdmissions =

VAR ranktable =

    ADDCOLUMNS (

        'sample',

        "first_Date", CALCULATE ( MIN ( 'sample'[FullDate] ), ALLEXCEPT ( 'sample', 'sample'[PatientID] ) ),

        "DayRank", RANKX (

            FILTER (

                'sample',

                'sample'[PatientID] = EARLIER ( 'sample'[PatientID] )

                    && 'sample'[FullDate] = EARLIER ( 'sample'[FullDate] )

            ),

            'sample'[Index],,

            ASC,

            Dense

        )

    )

VAR result =

    SUMX (

        FILTER (

            ranktable,

            'sample'[FullDate] > [first_Date]

            || ( 'sample'[FullDate] = [first_Date] && [DayRank] > 1 )

        ),

        [AdmitCount]

    )

RETURN

    COALESCE(result, 0)

total_admissions = [NewAdmission] + [ReAdmissions]

Then, to filter the dates in the table, you need to create this measure:

Table_Filter =

    VAR year = SELECTEDVALUE(DateDim[YEAR])

    VAR MONTH = SELECTEDVALUE(DateDim[Month_Num])

    VAR START_OF_MONTH  = DATE(year, MONTH, 1)

    VAR END_OF_MONTH = EOMONTH(START_OF_MONTH, 0)

    var selected_date = SELECTEDVALUE('sample'[FullDate])

    RETURN

        IF ( selected_Date >= START_OF_MONTH && selected_date <= end_of_month, 1, 0)

And you need to add this as a visual-level filter, only include rows where it equals 1.

Attached is the .pbix file.

///MEDIOCRE POWER BI ADVICE, BUT IT'S FREE///