How to continuously subtract/add column values from a single number

[alya1]

Hello,

I created 2 tables (via New Table -> DAX) with the columns below:

Add

Year	Month	Add Number
2020	Jan	9
2020	Feb	7
2020	Mar	3
...
2025	Dec	6

Subtract

Year	Month	Subtract Number
2020	Jan	4
2020	Feb	5
2020	Mar	8
...
2025	Dec	6

I have another table that I got distinct count of customers from Dec 31st 2019. Let's say it is 100 for simplicity sake. 

How can I calculate a new column for rolling monthly customer count like below:

Year	Month	Customer Count
2020	Jan	105 (100+9-4)
2020	Feb	107 (105+7-5)
2020	Mar	102 (107+3-8)
...
2025	Dec	### (### from previous month+6-6)

Thank you!

[gmsamborn · Accepted solution]

Hi @alya1 

In Power BI there is no easy way to refer to a "previous row", this is generally done by adding all numbers to date for both numbers to be added as well as for numbers to be subtracted and add those to the beginning balance.

In my example pbix below, I accomplish this by using separate measures or a single measure like this:

Balance = 
VAR _BegBal = MAX( 'BegBal'[Balance] )   // you will need to use your calculation
VAR _Curr = MAX( 'Date'[Date] )
VAR _Added =
    CALCULATE(
        SUM( 'Add'[Number] ),
        'Date'[Date] <= _Curr
    )
VAR _Subtracted =
    CALCULATE(
        SUM( 'Subtract'[Number] ),
        'Date'[Date] <= _Curr
    )
VAR _Result = _BegBal + _Added - _Subtracted
RETURN
    _Result

How to continuously subtract or add column values from a single value.pbix

Let me know if you have any questions.

(I'm pretty sure this could be done using  WINDOW  as well if you want.)