We are looking to replcate a process we used in Excel and have it work in Power BI.
Cell E2 = 4 + D2
*NOTE: This formula is meant to say we had 4 people on the 23, but we do not display that and we want to add that to the 1 person on the 24 for a total of 5 active student cases.
Cell E3 = ( E2 + D3 ) - F3
*NOTE: This formual is repeated for all other rows. It says that we take the active cases from the pervious day (E2) add in the new cases from today (D3) and subtract any recovered cases from today (F3).
We repeat this process for Employees. Then, add the active student cases to the active employee cases to create the total active cases for the day (J2)
Cell J2 = E2 + E6.
The end result does not have to be formatted like the table below, but we want to be able to use the dayily total active cases in a Card. And, we would prefer to only enter in the new and recovered cases and have everything else calculate in Power BI.
| | A | B | C | D | E | F | J |
| 1 | Term | Community Member | Date | New | Active | Recovered | Total Active |
| 2 | Fall 2020 | Student | 8/24/2020 | 1 | 5 | 0 | 9 |
| 3 | Fall 2020 | Student | 8/25/2020 | 1 | 6 | 0 | 8 |
| 4 | Fall 2020 | Student | 8/26/2020 | 3 | 9 | 0 | 11 |
| 5 | Fall 2020 | Student | 8/27/2020 | 0 | 9 | 0 | 11 |
| 6 | Fall 2020 | Employee | 8/24/2020 | 0 | 4 | 0 | |
| 7 | Fall 2020 | Employee | 8/25/2020 | 0 | 2 | 2 | |
| 8 | Fall 2020 | Employee | 8/26/2020 | 0 | 2 | 0 | |
| 9 | Fall 2020 | Employee | 8/27/2020 | 0 | 2 | 0 | |
