How to compare entries with the previous week

[Serj]

Hi All,

I have a set of weekly data and want to compare any given week with the previous one.

Week	Application	User access	Priority
w1	App1	Full	1
w1	App2	Full	1
w1	App3	Read Only	2
w1	App4	Read Only	2
w2	App1	Full	1
w2	App2	Read Only	1
w2	App5	Full	1
w3	App1	Full	2
w3	App2	Read Only	1
w3	App3	Read Only	1
w3	App5	Read Only	2
w4	App1	Full	2
w4	App2	Read Only	1
w4	App3	Read Only	1
w4	App5	Read Only	2

The output should list just those Applications with changed User Access or Priority parameters with respect to the previous week and include values for those parameters as well.

For example, for w2 it should be as follows:

Application	User Access (current week)	Priority (current week)	User Access (previous week)	Priority (previous week)
App2	Read Only	1	Full	1
App3	-	-	Read Only	2
App4	-	-	Read Only	2
App5	Full	1	-	-

For w3 it should be as follows:

Application	User Access (current week)	Priority (current week)	User Access (previous week)	Priority (previous week)
App1	Full	2	Full	1
App3	Read Only	1	-	-
App5	Read Only	2	Full	1

For w4 it should be blank as there are no changes from w3:

Application	User Access (current week)	Priority (current week)	User Access (previous week)	Priority (previous week)

Could anyone help me with that, please? Much appreciated!

[v-linyulu-msft · Accepted solution]

Thanks for the reply fromTutu_in_YYC , please allow me to provide another insight:

Hi, @Serj 

Thanks for letting us know your user experience.  According to your case description, I do understand how frustrated you are now.Regarding the issue you raised, my solution is as follows:

1.Firstly, you need to create a calculation table that includes all apps for each week, and establish a weeknum calculated column for easier future reference.

All =
VAR week1 =
    DISTINCT ( 'Table'[Week] )
VAR app1 =
    DISTINCT ( 'Table'[Application] )
RETURN
    CROSSJOIN ( week1, app1 )

weeknum = VALUE(RIGHT('All'[Week],1) )

2.Use the week column to create a slicer.

3.Secondly, create the following calculated columns:

Priority (current week) =
LOOKUPVALUE (
    'Table'[Priority],
    'Table'[Week], 'All'[Week],
    'Table'[Application], 'All'[Application]
)

User Access (current week) =
LOOKUPVALUE (
    'Table'[User access],
    'Table'[Week], 'All'[Week],
    'Table'[Application], 'All'[Application]
)

4.Then, establish the following measures:

Priority (previous week) =
CALCULATE (
    MAX ( 'All'[Priority (current week)] ),
    FILTER (
        ALL ( 'All' ),
        'All'[weeknum]
            = MAX ( 'All'[weeknum] ) - 1
            && 'All'[Application] = MAX ( 'All'[Application] )
    )
)

User Access (previous week) =
CALCULATE (
    MAX ( 'All'[User Access (current week)] ),
    FILTER (
        ALL ( 'All' ),
        'All'[weeknum]
            = MAX ( 'All'[weeknum] ) - 1
            && 'All'[Application] = MAX ( 'All'[Application] )
    )
)

diff1 =
IF (
    MAX ( 'All'[Priority (current week)] ) = 'All'[Priority (previous week)]
        && MAX ( 'All'[User Access (current week)] ) = 'All'[User Access (previous week)],
    0,
    1
)

5.Next, apply the calculated columns and measures to the visualisation objects, with particular emphasis on applying the measure diff1 to the filters.

6.Here's my final result, which I hope meets your requirements.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Serj]

Hi @v-linyulu-msft , amazing explanation!  Thank you so much for your time, that really helped and I was able to apply your solution to my data. 

[Tutu_in_YYC]

Hi, to do it right, you will need the start of week date, instead of just w1 vs w2. Then you can use dax time intelligence function to perform the comparison.

See if this tutorial helps
https://www.youtube.com/watch?v=F78emhTBX88