Calculate Week over Week % change based on Number of weeks since event

[Anonymous]

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)

[AlB]

@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 )

[Anonymous]

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"

[AlB]

Hi @Anonymous

Is 'Avg Activities per Visit WoW%' a measure or a calculate column?

[Anonymous]

@AlB I have it as a measure.  Same with Avg Activities per Visit.

[AlB]

@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

[Anonymous]

@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

[v-cherch-msft]

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

[Anonymous]

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

[v-cherch-msft]

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

[v-cherch-msft · Accepted 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