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.
I have a column that calculates the number of weeks since an activity occured:
Weeks Since Last PV = DATEDIFF(Activity[Last PV Before Activity],Activity[Date],WEEK )
I use this field to evaluate avg activities performed based on the number of weeks since the last visit.
What I want to do is then see the WoW change for the avg activity. I tried the below but I am not getting the correct answer. It seems like the problems lies in referencing the week before the current one being evaluated. Specifically, I am not able to calculate the activity, but filter for only the avg from the previous week.
Avg Activities per Visit WoW% = VAR __PREV_WEEK = CALCULATE([Avg Activities per Visit], Activity[Weeks Since Last PV] - 1) RETURN DIVIDE(([Avg Activities per Visit] - __PREV_WEEK), __PREV_WEEK)
Solved! Go to Solution.
Hi @Anonymous
Try this:
Avg Activities per Visit WoW% = VAR __PREV_WEEK = CALCULATE ( [Avg Activities per Visit], FILTER ( ALL ( Activity ), Activity[Weeks Since Last PV] = MAX ( Activity[Weeks Since Last PV] ) - 1 ) ) RETURN IF ( MAX(Activity[Weeks Since Last PV])=1, BLANK (), DIVIDE ( ( [Avg Activities per Visit] - __PREV_WEEK ), __PREV_WEEK ) )
Regards,
Cherie
@Anonymous
Your logic for the WOW% is fine It's the code that isn't, I think.
I'm a bit confused and would need to know more about the structure and fields of your data table and the code of [Avg Activities per visit] but it looks like there's something wrong with the filter argument in your CALCULATE in [Avg Activities per Visit WoW]. If set up as a measure, what you show should give an error if Activity[Weeks Since Last PV] is a column. Doesn't it?
If you're to set up Activity[Weeks Since Last PV] in the rows of a matrix like it seems that you have in the capture you show, try this:
Avg Activities per Visit WoW% = VAR __PREV_WEEK = CALCULATE ( [Avg Activities per Visit], Activity[Weeks Since Last PV] = SELECTEDVALUE ( Activity[Weeks Since Last PV] ) - 1 ) RETURN DIVIDE ( ( [Avg Activities per Visit] - __PREV_WEEK ), __PREV_WEEK )
Yes this problem is a bit complicated without knowing the structure of the data. I tried your suggested code but am getting an error "a function 'SELECTEDVALUE" has been used in a T/F expressions that is used as a table filter expression"
Hi @Anonymous
Is 'Avg Activities per Visit WoW%' a measure or a calculate column?
@Anonymous
Ok. Would you explain to me what you are trying to do with:
CALCULATE([Avg Activities per Visit], Activity[Weeks Since Last PV] - 1)
It looks a bit strange
@AlB I am trying to create a variable which represents the avg activities that occured the week before the row being looked at. This is so that once I get that number, I can calculate the % difference between 1 and 2weeks, 2 and 3 weeks, etc.
This may not even be the way to achieve what I am looking for. I ultimatly just want to calculate the WoW % change for avg activities per visit like in the chart I posted above,, just WoW % change
Hi @Anonymous
You may try with below measure. Here is the similar post for your reference.
Avg Activities per Visit WoW% = VAR __PREV_WEEK = CALCULATE ( [Avg Activities per Visit], FILTER ( ALL ( Activity ), Activity[Weeks Since Last PV] = MAX ( Activity[Weeks Since Last PV] ) - 1 ) ) RETURN DIVIDE ( ( [Avg Activities per Visit] - __PREV_WEEK ), __PREV_WEEK )
Regards,
Cherie
Hi Cherie @v-cherch-msft,
This solution worked, thank you! I created another measure (Last Week Activity) which is just the portion of code related to the variable to make sure it was referencing the right number, which it is. The ony thing is that for Week 1, I don't want any comparison. I didn't mention this piece, my apologies. Would there be a way to omit comparing week 1 to week 0 (1,237 number).
Best,
Stephen
Hi @Anonymous
Could you tell me if your problem has been solved? If it is,kindly mark my answer as a solution if you feel that makes sense. Welcome to share your own solution. More people will benefit from here.
Regards,
Cherie
Hi @Anonymous
Try this:
Avg Activities per Visit WoW% = VAR __PREV_WEEK = CALCULATE ( [Avg Activities per Visit], FILTER ( ALL ( Activity ), Activity[Weeks Since Last PV] = MAX ( Activity[Weeks Since Last PV] ) - 1 ) ) RETURN IF ( MAX(Activity[Weeks Since Last PV])=1, BLANK (), DIVIDE ( ( [Avg Activities per Visit] - __PREV_WEEK ), __PREV_WEEK ) )
Regards,
Cherie
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |