How to transfer the current month value to next month.

[adi_dasturkar]

At start till date "Active patients" are 0. Then "Total Active patients" = "Active Patients" + "New Patients" - "Drop off Patients".
And 2019 November's "Total Active patients" i.e 2 which is the December's "Active Patient".  And 2019 December "Total Active Patients" i.e 4 which is 2020 January's "Active Patients" and so on... 

Date	Active Patients	New Patients	Drop off Patients	Total Active Patients
Nov-19	0	3	1	2
Dec-19	2	2		4
Jan-20	4	1	2	3
Feb-20	3	1		4
Mar-20	4	1		5
Apr-20	5	2	1	6
May-20	6	10	2	14
Jun-20	14	4	1	17

How do I calculate "Active Patients" column In Power BI Using Dax.

Please Help me.


Thanks In Advance!

[v-shex-msft · Accepted solution]

Hi @adi_dasturkar,

I think you can try to use the following calculated column formula to do aggregate on new and drop patient amount.

Active Patients =
SUMX (
    FILTER (
        SUMMARIZE ( Table, [Date], "Actived", [New Patients] - [Drop off Patients] ),
        [Date] < EARLIER ( Table[Date] )
    ),
    [Actived]
)+0

Regards,

Xiaoxin Sheng

[lbendlin]

This measure works for the individual rows, it will need to be adjusted for the totals.

TotalPatients = sumx(Patients,Patients[Active Patients]+Patients[New Patients]-Patients[Drop off Patients])