line graphs based on data with two dates

[petermb72]

We are a healthcare facility and we are looking for tracking the number of people through the facility.  To get that we take the total number of people in the facility on the first of the month and then add all the admissions for the month to the total that we had on the first.  I want to be able to present the data from month to month in a line graph or a bar chart.  Here is what a sample of the data looks like:

Resident Number	Census Date	Admission Date	Notes:
1234	1/1/2023	10/5/2022	count 1st of month
1111	1/1/2023	12/15/2022	count 1st of month
1234	1/2/2023	10/5/2022	don't count
5555	1/2/2023	1/2/2023	count new admit
1111	1/2/2023	12/15/2022	don't count
1234	1/3/2023	10/5/2022	don't count
5555	1/3/2023	1/2/2023	don't count
1111	1/3/2023	12/15/2023	don't count
1234	1/4/2023	10/5/2022	don't count
5555	1/4/2023	1/2/2023	don't count
7891	1/5/2023	1/5/2023	count new admit
1234	1/5/2023	10/5/2022	don't count
5555	1/5/2023	1/2/2023	don't count
7891	1/30/2023	1/5/2023	don't count
1234	1/30/2023	10/5/2023	don't count
5555	1/30/2023	1/2/2023	don't count

With the above sample we would have a total number of residents through the facility of 4 (two residents present on the 1st and then two new admits)for January.  This data would continue will all of the days of the year in the file.   Any help on how to get the number of residents on the 1st plus the number of residents admitted durring that month and then presenting it as a graph of month to month for the year, that would be awesome.

Peter

[v-yangliu-msft]

Hi  @petermb72 ,

Here are the steps you can follow：

1. Create measure.

Measure =
CALCULATE(DISTINCTCOUNT('Table'[Resident Number]),
    FILTER(ALL('Table'),
    YEAR('Table'[Census Date])=YEAR(MAX('Table'[Census Date]))&&MONTH('Table'[Census Date])=MONTH(MAX('Table'[Census Date]))&&
   'Table'[Census Date]=EOMONTH(MAX('Table'[Census Date]),-1)+1 ||
    'Table'[Census Date]='Table'[Admission Date])
)

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[lbendlin]

would a graphical solution help?

Any help on how to get the number of residents on the 1st plus the number of residents admitted durring that month and then presenting it as a graph of month to month for the year, that would be awesome.

Your sample data is insufficient for this.