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.
Hi all, I'm trying to show the Week-over-week % increase in a Matrix visualization. I created two Measures for each Metric I want to show, one for Last week, and one for Current week.
Adding this to the Matrix visual, however, is very kludgy and not suitable for what I want. If I remove the Column Grand Totals, the % increase also disappears. It is also very cumbersome to create these measures for each metric. Is there a better way?
CurrWeekUsers = CALCULATE(SUM(data_source[users]), FILTER(google_analytics_4weeks, data_source[week_num]=WEEKNUM(NOW() )))
PreWeekUsers = CALCULATE(SUM(data_source[users]), FILTER(google_analytics_4weeks, data_source[week_num]=WEEKNUM(NOW() )-1))
% Change = ([CurrWeekUsers] - [PreWeekUsers] ) / [PreWeekUsers]
Hi there
What I would suggest doing is to put all your measures into one measure and then debug them using the variables. You can watch this video by Adam and Marco who explain how to do the debugging.
https://www.youtube.com/watch?v=9SV2VnYbgg4
I would also suggest creating a Date table, as this makes Time Intelligence measures work.
You can find details on how to create it from my blog post here: https://www.fourmoo.com/2016/09/13/power-bi-how-to-easily-create-dynamic-date-tabledimension-with-fi...
Then below is the measure that I created on my Google Analytics and it works as expected.
% Weekly Change =
VAR ThisWeekDate =
WEEKNUM ( TODAY (), 2 )
VAR LastWeekDate =
WEEKNUM ( TODAY (), 2 ) - 1
VAR ThisYear =
YEAR ( TODAY () )
VAR CurrWeekUsers =
CALCULATE (
[Users],
FILTER (
'Date',
'Date'[Week Number of Year] = ThisWeekDate
&& 'Date'[Calendar Year] = ThisYear
)
)
VAR PrevWeekUsers =
CALCULATE (
[Users],
FILTER (
'Date',
'Date'[Week Number of Year] = LastWeekDate
&& 'Date'[Calendar Year] = ThisYear
)
)
VAR PercChange =
DIVIDE ( ( CurrWeekUsers - PrevWeekUsers ), PrevWeekUsers )
RETURN
PercChange
Thank you Gilbert, could you show an example of a chart or matrix visual using this measure? How does it look? For example, in mine, I'd like to display the last 4 weeks but only show the % change once, for the last 2 weeks.
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 |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |