cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper IV

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

Hello,

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

 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!

1 ACCEPTED SOLUTION
Super User

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] )
CALCULATE(
'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.)

Super User

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] )
CALCULATE(
'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.)

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.