- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Integrations with Files and Services
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Welcome to the Community
- Community Feedback
- Community Help Blog
- 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 calculate percentage change from prior date...

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

Highlighted

dancarr22

Regular Visitor

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

10-17-2017
03:17 PM

Hello,

How to calculate percentage change from prior date (prior record)?

So, in the example below we would want the 9/19 price minus the 9/18 price divided by the 9/18 price. (144.39-144.55)/144.55. then want this to work for every day (besides first day because there was no prior record)

This was very simple in Tableau - just had 'Percent Difference' as an option when displaying values.

Did see a 'Percent Different in 'Quick MEasures' but this seems to be looking for 2 different numeric fields.

Thanks,

Dan

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

v-ljerr-msft

Super Contributor

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

10-18-2017
08:04 PM

Hi @dancarr22,

Could you try using the formula below to create a new measure to see if it works in your scenario.

Measure = VAR previousDateWithPrice = CALCULATE ( MAX (Data[Date]), FILTER ( ALL (Data),Data[Price]> 0 &&Data[Date]< MAX (Data[Date]) ) ) VAR previousPrice = CALCULATE ( SUM (Data[Price]), FILTER ( ALL (Data),Data[Date]= previousDateWithPrice ) ) RETURN DIVIDE ( SUM (Data[Price]) - previousPrice, previousPrice )

Remark: You'll need to replace the **bold** with your real table name and column name.

Regards

4 REPLIES 4

Ashish_Mathur

Super User

Re: How to calculate percentage change from prior date (prior record)?

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

10-17-2017
07:00 PM

Hi,

Try this calculated field formula

=IFERROR([Amount]/CALCULATE(LASTNONBLANK(Data[Price],[Amount]>0),Data[Date]<MAX(Calendar[Date])),BLANK())

Here are my assumptions

- Amount is a calculated field formula =SUM(Data[Price])
- There is a calendar table. The date column in the Data table has a relatiosnhip to the Date column of the Calendar table
- Date in the visual have been dragged from the Calendar table

If my solution does not work, then share the link from where i can download your file.

v-ljerr-msft

Super Contributor

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

10-18-2017
08:04 PM

Hi @dancarr22,

Could you try using the formula below to create a new measure to see if it works in your scenario.

Measure = VAR previousDateWithPrice = CALCULATE ( MAX (Data[Date]), FILTER ( ALL (Data),Data[Price]> 0 &&Data[Date]< MAX (Data[Date]) ) ) VAR previousPrice = CALCULATE ( SUM (Data[Price]), FILTER ( ALL (Data),Data[Date]= previousDateWithPrice ) ) RETURN DIVIDE ( SUM (Data[Price]) - previousPrice, previousPrice )

Remark: You'll need to replace the **bold** with your real table name and column name.

Regards

mommabird

Visitor

Re: How to calculate percentage change from prior date (prior record)?

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

11-21-2017
04:08 PM

I have a similar problem to which this has been the closest solution I've found so far, but it's still not quite achieving what I need. I'm working with educational data which is parsed out in terms (i.e. 2015SP, 2015SU, 2015FA, 2016SP, 2016SU, etc.). For each term there are over a 1,000 sections for which we need to calculate total full-time equivalent enrollments and then compare the total enrollments from Fall to Fall and Spring to Spring.

How would I adjust your code to achieve this? Here's an example of a matrix I'm trying to create. The values in the measure field are not correct. They should be -4.3% and -4.0%.

Cheers!

Ashish_Mathur

Super User

Re: How to calculate percentage change from prior date (prior record)?

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

11-22-2017
03:47 PM

Hi,

Share some data and show the expected result - how does only know when the term starts/ends?