Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Hi there,
Could you share your calculations? And perhaps some more info on your datamodel?
Robbe
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |