Incremental count/running total based on max in department

[smp45]

Hi

I have a table as follows, which shows by date and hour

• the number of patients admitted,
• number of patients discharged ,
• the difference between them (admittedpts (3) - dischargedpts (4) = (-1) net. 

There are a total of 65 patient in beds at 12midnight

I need to calculate a running total (add/take the net difference from the next hour's pts count) based on each hour. e.g. for 00:00-00:59 , 3 patients were admitted and 4 discharged which taken from 65 gives 64

so

    for 02:00-02:59 the number of patients should be (64+3admitted)=67,

    for 03:00-03:59 the number of patients should be (64+3admitted+1admitted) =68 patients

    for 04:00-04:59 the number of patients should be (64+3admitted+1admitted+ 2admitted) =70 patients

easy enough to do in excel, but in power bi 9bit more difficult) any help apprecited

[v-xiaoyan-msft]

Hi @smp45 ,

I'm not quite sure if I understand your needs accurately.
Sample data without sensitive information and expected output would help tremendously.
Please see this post regarding: How-to-provide-sample-data-in-the-Power-BI-Forum 

Hope it helps,

Community Support Team _ Caitlyn

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

[smp45]

Hi

I have 2 tables , Table A holds the number of admissions and discharges by Date and hour

                           Table B Holds the number of patients in a bed at 12 midnight (this is a static number)

As we go on during the night and day, new patients will be admitted and some existing patients will be discharged.

What i require is a running total each hour that takes into account the number admitted and discharged by hour

here is a screen shot of the data.

Table A

Date	Hour_A	Hour Band	Admit	Discharges	Net
12/11/2021	0	00:00-00:59	4	4	0
	1	01:00-01:59	1	1	0
	2	02:00-02:59	4	1	3
	3	03:00-03:59	4	5	-1
	4	04:00-04:59	2	1	1
	5	05:00-05:59	4	2	2
	6	06:00-06:59	2	3	-1
	7	07:00-07:59	3	1	2
	8	08:00-08:59	4		4
	9	09:00-09:59	1	1	0
	10	10:00-10:59	3	3	0
	11	11:00-11:59	2		2
	12	12:00-12:59		3	-3
	13	13:00-13:59	4	5	-1
	14	14:00-14:59	4	1	3
	15	15:00-15:59	2		2
	16	16:00-16:59	4	3	1
	17	17:00-17:59	2	4	-2
	18	18:00-18:59		8	-8
	19	19:00-19:59	5	9	-4
	20	20:00-20:59	2	1	1
	21	21:00-21:59	6	1	5
	22	22:00-22:59	4	6	-2
	23	23:00-23:59	5	2	3
13/11/2021	0	00:00-00:59	2	3	-1
	1	01:00-01:59	1	4	-3
	2	02:00-02:59	4	2	2
	3	03:00-03:59	3		3
	4	04:00-04:59	3	2	1
	5	05:00-05:59	2	2	0
	6	06:00-06:59	1	1	0
	7	07:00-07:59	1		1
	8	08:00-08:59	4		4
	10	10:00-10:59		2	-2
	11	11:00-11:59	4	5	-1
	12	12:00-12:59	1	1	0
	13	13:00-13:59	3	4	-1
	14	14:00-14:59	4		4
	15	15:00-15:59	1	2	-1
	16	16:00-16:59	1	3	-2
	17	17:00-17:59	3	2	1
	18	18:00-18:59	3	8	-5
	19	19:00-19:59	2	6	-4
	20	20:00-20:59	6	2	4
	21	21:00-21:59	3	4	-1
	22	22:00-22:59	5	1	4
	23	23:00-23:59	2	2	0

Table B

Date	Hour	Hour_A	NumberOfPatients
12/11/2021	00:00:00	0	62

The desired result i.e - running total

hope this makes it clear