Forum Discussion

Tihannah's avatar
Tihannah
Resolver II
3 years ago
Solved

Matrix Table Layout (YOY,MOM, Several Measures)

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.

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.com)

    Regards,

    Xiaoxin Sheng

    • Tihannah's avatar
      Tihannah
      Resolver II
      3 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        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