Matrix Visual

[Anonymous]

Hi there,

I have a Matrix Displaying Products in the rows, week (formatted as text eg. "W02 2020") which comes from a table called date.

I would now like to calculate the difference between the most current week and the week before that and only insert it once (after the column containing the data of the current week)

I do can calculate the difference between two weeks bu the matrix visual always insertes a delta column after each week (see below)

Prodcut	w 52 2019	delta w 52 to w 51	w 01 2020	delta w01 to w 52	w 02 2020	delta w02 to w 01
x	5	1	7	2	11	4
y	10	2	11	1	10	-1

Is there a way to use the matrix visual and flexible hide the delta columns that are not based on the two most current weeks in the data set so that the matrix looks like this:

Product	w 52 2019	w 01 2020	w 02 2020	delta w 02 to w 01
x	5	7	11	+4
y	10	11	10	-1

[v-lionel-msft]

Hi @Anonymous ,

How about adding filters? You can add filters manually or using the FILTER function.

Best regards,
Lionel Chen

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

[Mariusz]

Hi @Anonymous 

You can try this pattern.

Previous Week = 
VAR __week = SELECTEDVALUE( 'Calendar'[yearWeekNo] ) -- format is number YYYYWW 202001
VAR __previousWeek =
    CALCULATE(
        MAX( 'Calendar'[Year Week] ),
        ALL( 'Calendar' ),
        'Calendar'[yearWeekNo] < __week
    )
RETURN 
CALCULATE(
    [Sales],
    ALL( 'Calendar' ),
    'Calendar'[Year Week] = __previousWeek
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

Hi @Mariusz 

 would you expect the outcome of your formula is in my matrix? Do I sue the meausre as a column in my matrix?

Calendar Year Week in my model is currently a text in the format e.g. "W 01 2020"