cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
FarisG
Regular Visitor

Value Difference From Previous Period

Hi guys,

Hope all is well.

 

I am trying to calculate the % of difference between a value at a certain period and the value in the previous period (Quarter, Year). 

Here is the Matrix that I currently have

FarisG_0-1634211074999.png

Using the example I am showing, I want to be able to show the difference between 2015 Quarter 4 (21.19M) and 2015 Quarter 3 (13.18M) or the difference between 2016 and 2015 if I move up to the Year Hierarchy.  I am not currently using a Calendar table I am just using the Date column I have in the data and created a hierarchy from it.

 

Kindly find a dummy example of the columns used in Matrix:

IDReference dateCategoryValue
50403/08/2015Capital Services2,658.00
50515/09/2015Capital Services3,046.00
50606/11/2015Capital Services1,614.00
50703/08/2016Capital Services4,138.00
50825/08/2016Capital Services1,088.00
50913/10/2016Capital Services1,529.00
51003/01/2017Capital Services10,007.00
51722/08/2015Process Maintenance1,366.00
51811/09/2015Process Maintenance1,599.00
51924/12/2015Process Maintenance1,655.00
52015/04/2016Process Maintenance16,594.00
52112/04/2017Process Maintenance17,094.00

 

Would really appreciate the help!

 

Many thanks,

1 ACCEPTED SOLUTION
V-pazhen-msft
Community Support
Community Support

@FarisG 
You must have a calendar to achieve this. You can compare current quarter with previous period using measures, but you cannot make it automatically change based on your date hierarchy.

 

For example:

vs previous quarter =

var current = calculate(sum([value]), filter(allselected(table),[Category]=max[Category]))
var prequarter=calculate(current, Previousquarter(table,[calendar date]))
Return  current - prequarter

 

For other periods, you can replace the previous period with following function.

PREVIOUSQUARTER function (DAX) - DAX | Microsoft Docs

PREVIOUSYEAR function (DAX) - DAX | Microsoft Docs

PREVIOUSMONTH function (DAX) - DAX | Microsoft Docs

 

 

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

2 REPLIES 2
V-pazhen-msft
Community Support
Community Support

@FarisG 
You must have a calendar to achieve this. You can compare current quarter with previous period using measures, but you cannot make it automatically change based on your date hierarchy.

 

For example:

vs previous quarter =

var current = calculate(sum([value]), filter(allselected(table),[Category]=max[Category]))
var prequarter=calculate(current, Previousquarter(table,[calendar date]))
Return  current - prequarter

 

For other periods, you can replace the previous period with following function.

PREVIOUSQUARTER function (DAX) - DAX | Microsoft Docs

PREVIOUSYEAR function (DAX) - DAX | Microsoft Docs

PREVIOUSMONTH function (DAX) - DAX | Microsoft Docs

 

 

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

lbendlin
Super User
Super User

" I am not currently using a Calendar table " - you may want to correct that.  You need a contiguous, covering calendar table to do date math.

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors
Top Kudoed Authors