Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
93 | |
86 | |
80 | |
69 | |
68 |
User | Count |
---|---|
226 | |
129 | |
119 | |
83 | |
77 |