cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
swapnil_022 Frequent Visitor
Frequent Visitor

How to subtract current row from prior row and so on in power bi

I have one calculated table which has following fields.

 

   Year          Revenue

  2005          200

  2006          300

  2007          400

  2008          300

 

Above table is generated from following DAX

 

revenue_summary = SUMMARIZE('WA_Retail-SalesMarketing_-ProfitCost',[Year],"Total Revenue",SUM('WA_Retail-SalesMarketing_-ProfitCost'[Revenue]))

 

My desrired table would be

 

  

  Year          Revenue       Differrence

  2005          200              0

  2006          300              (300-200)= 100

  2007          400              (400-300)=100

  2008          300              (300-400)=-100

 

How can I achieve this power BI?

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-sihou-msft
Moderator

Re: How to subtract current row from prior row and so on in power bi

@mattbrice

 

You can't directly call the column in calculated table and deliver its table context into the new summarized column

 

"Difference", [Total Revenue]
        - CALCULATE (
            SUM ( 'WA_Retail-SalesMarketing_-ProfitCost'[Revenue] ),
            Table[Year] - 1
        )

 

And if you have the source [Year] column referenced, it will just calculate the Current Year Revenue minus the Total.

 

Capture2.PNG

 

In this scenario, we have to create the Difference measure/column individually since our calculation is based on the generated "revenue_summary" table.

 

Difference = 
var CurrentYear = MAX('Table'[Year])
return
CALCULATE(SUM('Table'[Total Revenue]),FILTER(ALL('Table'),'Table'[Year]=CurrentYear-1))

Capture1.PNG

 

 

Then we may create another calculated column to summarize the Difference measure into a column.

 

Regards,

7 REPLIES 7
prathy Regular Visitor
Regular Visitor

Re: How to subtract current row from prior row and so on in power bi

Hi,

 

Once you created a Calculated Table, you can create a Calculated Column with follwoing expression:

 

Exp = CALCULATE(SUM('Table'[R]))-if(CALCULATE(MAX('Table'[R]),FILTER('Table','Table'[Y]<EARLIER('Table'[Y],1)))=0,0,CALCULATE(MAX('Table'[R]),FILTER('Table','Table'[Y]<EARLIER('Table'[Y],1))))

 

To get Previous Row value, we are using EARLIER function.

CALCULATE(MAX('Table'[R]),FILTER('Table','Table'[Y]<EARLIER('Table'[Y],1)))

 

Hope this helps.

 

Regards,

Prathy

mattbrice Senior Member
Senior Member

Re: How to subtract current row from prior row and so on in power bi

@prathy not sure i understand your code...EARLIER retrieves the current value of Table[Y] of the outer loop in an iteration; not the previous row value.  

 

Dax is an aggreation tool so referencing the previous row is not really done.  Instead we calculate the previous year value instead.  To add to your code I would do:

 

revenue_summary =
ADDCOLUMNS (
    SUMMARIZE (
        'WA_Retail-SalesMarketing_-ProfitCost',
        [Year],
        "Total Revenue", SUM ( 'WA_Retail-SalesMarketing_-ProfitCost'[Revenue] )
    ),
    "Difference", [Total Revenue]
        - CALCULATE (
            SUM ( 'WA_Retail-SalesMarketing_-ProfitCost'[Revenue] ),
            Table[Year] - 1
        )
)
prathy Regular Visitor
Regular Visitor

Re: How to subtract current row from prior row and so on in power bi

@mattbrice, Yes you are right. Whatever I suggested works for the given sample values. I didn't think it works in all the cases.

 

Moderator v-sihou-msft
Moderator

Re: How to subtract current row from prior row and so on in power bi

@mattbrice

 

You can't directly call the column in calculated table and deliver its table context into the new summarized column

 

"Difference", [Total Revenue]
        - CALCULATE (
            SUM ( 'WA_Retail-SalesMarketing_-ProfitCost'[Revenue] ),
            Table[Year] - 1
        )

 

And if you have the source [Year] column referenced, it will just calculate the Current Year Revenue minus the Total.

 

Capture2.PNG

 

In this scenario, we have to create the Difference measure/column individually since our calculation is based on the generated "revenue_summary" table.

 

Difference = 
var CurrentYear = MAX('Table'[Year])
return
CALCULATE(SUM('Table'[Total Revenue]),FILTER(ALL('Table'),'Table'[Year]=CurrentYear-1))

Capture1.PNG

 

 

Then we may create another calculated column to summarize the Difference measure into a column.

 

Regards,

giansaavedra Frequent Visitor
Frequent Visitor

Re: How to subtract current row from prior row and so on in power bi

Apologies, this post is not to answer the question but rather to present two similar queries. I am totally new to Power BI and was hoping that me and my colleague could get some help.

 

1. Add latest row value to prior row value. In this table, what would be the appropriate DAX syntax for the sum of error column?

 

Date_Field                    Error            Sum of Error

10/17/2017 0:00            0.2                  0.2

10/17/2017 0:05            0.1                  (0.1 + 0.2) =0.3

10/17/2017 0:10            0.5                  (0.5 + 0.3) = 0.8

 

Note:

1. Date_Field contains both date and time, will there be any synatx difference compared to date only?

2. Input data is from a live SQL database that updates value every 5 minutes (as seen in the Date_Field) in real time continuously.

 

2. We also need to provide a percentile value for the latest row with all prior error values considered. What would be the DAX syntax for the 95% percentile comlumn? Formula for percentile in excel is PERCENTILE(array,k) where array is the range of E(E1 to E3) and k is 0.95.

 

Date_Field                    Error                               95% Percentile

10/17/2017 0:00            0.2   (E1)               PERCENTILE(E1,0.95) = 0.2

10/17/2017 0:05            0.1   (E2)               PERCENTILE(E1:E2,0.95) = 0.195

10/17/2017 0:10            0.5   (E3)               PERCENTILE(E1:E3,0.95) = 0.47

 

Thank you very much.

EmanuelTavares Frequent Visitor
Frequent Visitor

Re: How to subtract current row from prior row and so on in power bi

Hi @v-sihou-msft,

 

Congrats for the solution.

 

Can I take ask for a complementary demand on the same idea?

 

As the original demand, I need to execute the difference between two dates located in different rows, but I need to consider a key. It is something like this:

 

Key                     Date

1                       01/08/2018

1                       12/09/2018

1                        05/10/2018

2                       02/07/2018

2                        16/08/2018

 

Thanks a lot in advance.

 

Regards.

AnthonyC Frequent Visitor
Frequent Visitor

Re: How to subtract current row from prior row and so on in power bi

I am not sure what i am doing wrong, but the formula repeats the same value over and down the table and even fills the value at the top(no offset)