QoQ alongwith MoM view in one Matrix

[Saibal_78]

I have spend data which I want to create QoQ and MoM variance analysis, I created below view with QoQ and MoM in separate tables which are working fine

Now I want to have a combine view as shown below, any idea how do I do this? I am trying with Matrix view but its not working well, is it possible? Thanks in advance

[RobbeVL]

Hi there,

Could you share your calculations? And perhaps some more info on your datamodel?

Robbe

[Saibal_78]

Hi Robbe,

Here are the calculations

QoQ Analysis

Prev. Qtr. Spend =

Var CurrQtr = SELECTEDVALUE(DATA[Quarter No])

Var CurrYear = SELECTEDVALUE(DATA[Consumption Year])

Var MaxQtrNumber = CALCULATE(MAX(DATA[Quarter No]),ALL(DATA))

Return

SUMX(

FILTER(ALL(DATA),

IF(CurrQtr = 1, DATA[Quarter No] = MaxQtrNumber && DATA[Consumption Year] = CurrYear - 1,

    DATA[Quarter No] = CurrQtr - 1 && DATA[Consumption Year] = CurrYear)),

[Amount before Tax USD])

QoQ Change = IF(ISBLANK('DATA'[Prev. Qtr. Spend]),BLANK(),[Total Spend]-[Prev. Qtr. Spend])

QoQ % Change = DIVIDE([QoQ Change],[Prev. Qtr. Spend],0)

MoM Analysis

Prev. Mnth. Spend =

Var CurrMnth = SELECTEDVALUE(DATA[Consumption Month])

Var CurrYear = SELECTEDVALUE(DATA[Consumption Year])

Var MaxMnthNumber = CALCULATE(MAX(DATA[Consumption Month]),ALL(DATA))

Return

SUMX(

FILTER(ALL(DATA),

IF(CurrMnth = 1, DATA[Consumption Month] = MaxMnthNumber && DATA[Consumption Year] = CurrYear - 1,

    DATA[Consumption Month] = CurrMnth - 1 && DATA[Consumption Year] = CurrYear)),

[Amount before Tax USD])

MoM Change = IF(ISBLANK('DATA'[Prev. Mnth. Spend]),BLANK(),[Total Spend]-[Prev. Mnth. Spend])

MoM % Change = DIVIDE([MoM Change],[Prev. Mnth. Spend],0)

Data model has Consumption Date, Amount, Expenses Category and few derived columns such as consumption month, consumption quarter, consumption year