Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
Solved! Go to 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.)
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.)
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
18 | |
7 | |
6 | |
5 | |
5 |
User | Count |
---|---|
23 | |
10 | |
10 | |
9 | |
7 |