cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Saibal_78 Frequent Visitor
Frequent Visitor

QoQ alongwith MoM view in one Matrix

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

 


QoQ.PNGMoM.PNG

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

 

Combine.PNG

2 REPLIES 2
RobbeVL Established Member
Established Member

Re: QoQ alongwith MoM view in one Matrix

Hi there,

 

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

 

Robbe

Saibal_78 Frequent Visitor
Frequent Visitor

Re: QoQ alongwith MoM view in one Matrix

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 

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 235 members 2,794 guests
Please welcome our newest community members: