Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have weekly data as below for 3 years period and trying to create a measure that gives me a percentage difference with the previous time period (for example if I put data by QTR/Month/year)
Input Data -
Desired Output -
Is there any way to create this measure dynamic that works if drilled on the Month/Quarter/Year level on the visual? I have a normal table as below with SUM in it but we want it to be replaced by the percentage difference.
Solved! Go to Solution.
Hi @Anonymous ,
Try this measure, Quarter, week, etc. are written similarly.
aa =
VAR _lastyear =
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[Month] ),
YEAR ( [Date] )
= YEAR ( MAX ( 'Table'[Date] ) ) - 1
)
)
VAR _thisyear =
SUM ( 'Table'[Value] )
RETURN
DIVIDE ( _thisyear - _lastyear, _lastyear )
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous
Just change the Previousweek Variable Measure to which is mentioned the video
Proud to be a Super User!
ok so I just got something after using ALLSELECTED in the filter but still, I'm not able to get the correct total at the bottom?
@Anonymous
For Question 1 Watch this Video at 17:46 , where I have explained to see the week over week vairance analysis
https://www.youtube.com/watch?v=dLON3mj0los&list=PLWQB3PEUJKRmLf93pOxFQFIIe16ucwtW0&index=39
For the second question, is it possible to share some smaple data without any sensitive information
Proud to be a Super User!
Thank you that is helpful.
Can we create any measure with percentage difference comparing all 3 years ( 2019,20,21) on Month, Week, Quarter, Year level? like below.
Hi @Anonymous ,
Try this measure, Quarter, week, etc. are written similarly.
aa =
VAR _lastyear =
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[Month] ),
YEAR ( [Date] )
= YEAR ( MAX ( 'Table'[Date] ) ) - 1
)
)
VAR _thisyear =
SUM ( 'Table'[Value] )
RETURN
DIVIDE ( _thisyear - _lastyear, _lastyear )
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |