Count Active Patients at beginning of each month

[Anonymous]

I have seen a few examples but I cant wrap my head around it.

I have two tables:  TABLE1: ACCOUNTNO, ADMITDATE, DISCHARGEDATE

TABLE2: Date, MoYear

I want to count the active pateints at the begining of each month which is MoYear 1/1/2022 etc..

SO if a pateint was admitted on or before the first of each month and discharged after that day I want toc ount that patient in the month.

[amitchandak · Accepted solution]

@Anonymous , You need a measure like assume table 2 is date table and have join with ADMITDATE, if there is no join, we can remove cross filer

Current = CALCULATE(COUNTx(FILTER(TABLE1,TABLE1[Start Date]<=min('Date'[Date]) && (ISBLANK(TABLE1[DISCHARGEDATE]) || Employee[DISCHARGEDATE]>min('Date'[Date]))),(TABLE1[Employee Id ])),CROSSFILTER(TABLE1[ADMITDATE],'Date'[Date],None))

refer this blog, Max('Date'[Date]), means month end date

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970