calculating diferences between columns in matrix

[Mr_Stern]

Hi,

Still a beginer at BI, so having issues with this one, any help would be appreciated.

I have a matrix that I have built.

In the columns are the past 13 weeks, rows show clients, and then hours are the value.

I need to add a column at the end that will calculate the movement between the first week in the matrix and the last week in the matrix.

*Note that this does not show all the data, data goes back further than 13 weeks, it is just filtered to show the most recent quarter.

E.g. (just using 5 weeks for sample)

Client	W1	W2	W3	W4	W5	Movement(need help here)
Jane	14	20	28	32	20	6
Joe	72	48	12	18	12	(60)
Sam	78	78	70	70	78	0
Sue	16	30	40	40	40	24

[Anonymous · Accepted solution]

Hi @Mr_Stern 

Please try this:

Here I add your data:

Then add a measure:

MEASURE =
VAR _Vtable =
    SUMMARIZE (
        ALLSELECTED ( 'Table' ),
        'Table'[WeekNum],
        "WeekNo.", MID ( 'Table'[WeekNum], 2, 3 )
    )
VAR _diff =
    CALCULATE (
        SUM ( 'Table'[Hours] ),
        'Table'[WeekNum] = MAXX ( _Vtable, [WeekNum] )
    )
        - CALCULATE (
            SUM ( 'Table'[Hours] ),
            'Table'[WeekNum] = MINX ( _Vtable, [WeekNum] )
        )
RETURN
    IF (
        HASONEVALUE ( 'Table'[WeekNum] ),
        SUM ( 'Table'[Hours] ),
        IF ( _diff < 0, "(" & ABS ( _diff ) & ")", _diff )
    )

Then add a matrix:

In the format pane, change the Subtotal label in the Column subtotals:

The result is as follow:

Best Regards

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