- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Community Information
- Community Accounts & Registration
- Using the Community
- Community Feedback
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- How to subtract current row from prior row and so ...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

swapnil_022

Frequent Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-22-2017
03:25 AM

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?

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

v-sihou-msft

Moderator

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-23-2017
02:01 AM

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.

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

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

Regards,

7 REPLIES 7

prathy

Regular Visitor

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-22-2017
06:35 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-22-2017
07:14 AM

@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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-22-2017
08:39 AM

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

v-sihou-msft

Moderator

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-23-2017
02:01 AM

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.

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

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

Regards,

giansaavedra

Frequent Visitor

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-09-2017
04:56 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-23-2018
09:27 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-26-2018
02:24 PM

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)