Forum Discussion
How to transfer the current month value to next month.
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!
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] )+0Regards,
Xiaoxin Sheng
2 Replies
- lbendlin
Super User
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]) 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] )+0Regards,
Xiaoxin Sheng
