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 haven't had any luck, but hoping someone can assist with building a matrix table with the following layout? The business was previously using an excel template in this format. Currently it wants to give me all metrics under a single year.
Solved! Go to Solution.
HI @Tihannah,
You can take a look at the following sample formula and file if helps:
formula =
VAR currDate =
MAX ( 'Calendar'[Date] )
VAR prevDate =
DATE ( YEAR ( currDate ), MONTH ( currDate ) - 1,1 )
VAR currGroup =
SELECTEDVALUE ( 'Type'[Group] )
RETURN
IF (
currDate <= MAXX ( ALLSELECTED ( 'Sample' ), [Date] ),
SWITCH (
currGroup,
"Revenue",
CALCULATE (
SUM ( 'Sample'[Sales] ),
FILTER (
ALLSELECTED ( 'Sample' ),
YEAR ( [Date] ) = YEAR ( currDate )
&& MONTH ( [Date] ) = MONTH ( currDate )
)
),
"Expenses", 2,
"Profit",
CALCULATE (
SUM ( 'Sample'[Sales] ),
FILTER (
ALLSELECTED ( 'Sample' ),
YEAR ( [Date] ) = YEAR ( currDate )
&& MONTH ( [Date] ) = MONTH ( currDate )
)
)
- CALCULATE (
SUM ( 'Sample'[Sales] ),
FILTER (
ALLSELECTED ( 'Sample' ),
YEAR ( [Date] ) = YEAR ( prevDate )
&& MONTH ( [Date] ) = MONTH ( prevDate )
)
)
)
)
Regards,
Xiaoxin Sheng
HI @Tihannah,
You can create a matrix with calendar table month on rows and year on columns. Then you can create a new table with 'Revenue', 'Expenses','Profit' strings and use it on matrix columns above the year field.
After these steps, your will get a similar structure matrix as the snapshot. You can write a measure formula to extract and check current category field(month, year, custom group) values as condition to lookup raw table records and use switch function to redirect to different expressions based on current category.
Regards,
Xiaoxin Sheng
Can you create a sample in Power Bi? I don't fully understand.
HI @Tihannah,
You can take a look at the following sample formula and file if helps:
formula =
VAR currDate =
MAX ( 'Calendar'[Date] )
VAR prevDate =
DATE ( YEAR ( currDate ), MONTH ( currDate ) - 1,1 )
VAR currGroup =
SELECTEDVALUE ( 'Type'[Group] )
RETURN
IF (
currDate <= MAXX ( ALLSELECTED ( 'Sample' ), [Date] ),
SWITCH (
currGroup,
"Revenue",
CALCULATE (
SUM ( 'Sample'[Sales] ),
FILTER (
ALLSELECTED ( 'Sample' ),
YEAR ( [Date] ) = YEAR ( currDate )
&& MONTH ( [Date] ) = MONTH ( currDate )
)
),
"Expenses", 2,
"Profit",
CALCULATE (
SUM ( 'Sample'[Sales] ),
FILTER (
ALLSELECTED ( 'Sample' ),
YEAR ( [Date] ) = YEAR ( currDate )
&& MONTH ( [Date] ) = MONTH ( currDate )
)
)
- CALCULATE (
SUM ( 'Sample'[Sales] ),
FILTER (
ALLSELECTED ( 'Sample' ),
YEAR ( [Date] ) = YEAR ( prevDate )
&& MONTH ( [Date] ) = MONTH ( prevDate )
)
)
)
)
Regards,
Xiaoxin Sheng
Thank you so much!
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 |