Matrix Table Layout (YOY,MOM, Several Measures)

[Tihannah]

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.

[v-shex-msft]

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.

My Favorite DAX Feature: SELECTEDVALUE with SWITCH | Winston-Salem Power BI User Group (pbiusergroup...

Regards,

Xiaoxin Sheng

[Tihannah]

Can you create a sample in Power Bi? I don't fully understand.

[v-shex-msft · Accepted 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

[Tihannah]

Thank you so much!