Forum Discussion

alya1's avatar
alya1
Icon for Helper V rankHelper V
2 years ago
Solved

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

Hello,

 

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

Add

Year  Month  Add Number  
2020Jan9
2020Feb7
2020Mar3
...  
2025Dec6

Subtract

Year  Month  Subtract Number  
2020Jan4
2020Feb5
2020Mar8
...  
2025Dec6

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  
2020Jan105 (100+9-4)
2020Feb107 (105+7-5)
2020Mar102 (107+3-8)
...  
2025Dec### (### from previous month+6-6)


Thank you!

Need Help
  • gmsamborn's avatar
    gmsamborn
    2 years ago

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

     

1 Reply

  • gmsamborn's avatar
    gmsamborn
    Icon for Super User rankSuper User
    2 years ago

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