Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
User | Count |
---|---|
96 | |
85 | |
77 | |
67 | |
63 |
User | Count |
---|---|
110 | |
96 | |
96 | |
67 | |
59 |