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

alya1 — Wed, 03 Jul 2024 22:22:34 GMT

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!

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

gmsamborn — Thu, 04 Jul 2024 00:23:30 GMT

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.)

topic How to continuously subtract/add column values from a single number in DAX Commands and Tips

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

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