Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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:
ID | Reference date | Category | Value |
504 | 03/08/2015 | Capital Services | 2,658.00 |
505 | 15/09/2015 | Capital Services | 3,046.00 |
506 | 06/11/2015 | Capital Services | 1,614.00 |
507 | 03/08/2016 | Capital Services | 4,138.00 |
508 | 25/08/2016 | Capital Services | 1,088.00 |
509 | 13/10/2016 | Capital Services | 1,529.00 |
510 | 03/01/2017 | Capital Services | 10,007.00 |
517 | 22/08/2015 | Process Maintenance | 1,366.00 |
518 | 11/09/2015 | Process Maintenance | 1,599.00 |
519 | 24/12/2015 | Process Maintenance | 1,655.00 |
520 | 15/04/2016 | Process Maintenance | 16,594.00 |
521 | 12/04/2017 | Process Maintenance | 17,094.00 |
Would really appreciate the help!
Many thanks,
Solved! Go to Solution.
@Anonymous
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.
@Anonymous
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.
" 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.
User | Count |
---|---|
107 | |
88 | |
81 | |
76 | |
71 |
User | Count |
---|---|
112 | |
105 | |
96 | |
74 | |
66 |