Create week-to-week diff with month-to-month as subtotal in matrix table

[cj_oat]

Hi all,

I'm trying to create matrix table with weekly sales and column subtotal with monthly sales (I put 2 data in the columns field which are 1. Month and 2. Week), then I use below formula to create Week-to-Week diff, it works when looking at weekly column but when it comes to subtotal column in month, the number looks weird

is there any way that can make the subtotal column for month to show number of diff between total of this month vs total of previous month while still keep weekly column to show diff of Week-to-Week?

WTW =

VAR _previousweek =

    CALCULATE (

        MAX ( 'Table'[Week] ),

        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Week] < MAX ( 'Table'[Week] ) )

    )

VAR _previousvalue =

    CALCULATE (

        [Weekly Sales],

        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Week] = _previousweek )

    )

RETURN

    [Weekly Sales] - _previousvalue

[cj_oat]

Hi @rajendraongole1 

it works for me! Thank you so much for your advices!

[rajendraongole1 · Accepted solution]

Hi @cj_oat - You need to differentiate between row-level calculations (weekly differences) and subtotal-level calculations (monthly differences).

WTW =
VAR _isWeekLevel = ISINSCOPE('Table'[Week])
VAR _isMonthLevel = ISINSCOPE('Table'[Month])

VAR _previousWeek =
CALCULATE(
MAX('Table'[Week]),
FILTER(
ALLSELECTED('Table'),
'Table'[Week] < MAX('Table'[Week])
)
)

VAR _previousMonth =
CALCULATE(
MAX('Table'[Month]),
FILTER(
ALLSELECTED('Table'),
'Table'[Month] < MAX('Table'[Month])
)
)

VAR _previousWeekValue =
CALCULATE(
[Weekly Sales],
FILTER(
ALLSELECTED('Table'),
'Table'[Week] = _previousWeek
)
)

VAR _previousMonthValue =
CALCULATE(
[Weekly Sales],
FILTER(
ALLSELECTED('Table'),
'Table'[Month] = _previousMonth
)
)

RETURN
IF(
_isWeekLevel,
[Weekly Sales] - _previousWeekValue,
IF(
_isMonthLevel,
SUMX(
VALUES('Table'[Week]),
[Weekly Sales]
) - _previousMonthValue,
BLANK()
)
)

For the weekly columns, the measure will calculate the difference from the previous week.
For the monthly subtotal column, the measure will calculate the difference between the current month's total and the previous month's total.