Questions about Matrix, put custom values and pertentage into a Matrix Visual

Hi Jacob_Li ,

According to your description, here's my solution.

1.Create a new table.

Table =
UNION (
    ROW (
        "Month",
            "Prior to "
                & FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 1, 1 ), "MMM" )
    ),
    ROW (
        "Month", FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 1, 1 ), "MMM" )
    ),
    ROW (
        "Month", FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 2, 1 ), "MMM" )
    ),
    ROW (
        "Month", FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 3, 1 ), "MMM" )
    ),
    ROW (
        "Month", FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 4, 1 ), "MMM" )
    ),
    ROW (
        "Month", FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 5, 1 ), "MMM" )
    ),
    ROW ( "Month", "Total" ),
    ROW ( "Month", "Order%" )
)

In order to let the Month column sort as expected, create a calculated column in the new table.

Column =
SWITCH (
    'Table'[Month],
    "Prior to "
        & FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 1, 1 ), "MMM" ), 1,
    FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 1, 1 ), "MMM" ), 2,
    FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 2, 1 ), "MMM" ), 3,
    FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 3, 1 ), "MMM" ), 4,
    FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 4, 1 ), "MMM" ), 5,
    FORMAT ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) + 5, 1 ), "MMM" ), 6,
    "Total", 7,
    "Order%", 8
)

Result:

2.Create a measure.

Value =
SWITCH (
    MAX ( 'Table'[Column] ),
    1,
        COUNTROWS (
            FILTER (
                'Table (2)',
                'Table (2)'[Customer] = MAX ( 'Table (2)'[Customer] )
                    && MONTH ( 'Table (2)'[Order Date] ) <= MONTH ( TODAY () )
            )
        ) + 0,
    2,
        COUNTROWS (
            FILTER (
                'Table (2)',
                'Table (2)'[Customer] = MAX ( 'Table (2)'[Customer] )
                    && MONTH ( 'Table (2)'[Order Date] )
                        = MONTH ( TODAY () ) + 1
            )
        ) + 0,
    3,
        COUNTROWS (
            FILTER (
                'Table (2)',
                'Table (2)'[Customer] = MAX ( 'Table (2)'[Customer] )
                    && MONTH ( 'Table (2)'[Order Date] )
                        = MONTH ( TODAY () ) + 2
            )
        ) + 0,
    4,
        COUNTROWS (
            FILTER (
                'Table (2)',
                'Table (2)'[Customer] = MAX ( 'Table (2)'[Customer] )
                    && MONTH ( 'Table (2)'[Order Date] )
                        = MONTH ( TODAY () ) + 3
            )
        ) + 0,
    5,
        COUNTROWS (
            FILTER (
                'Table (2)',
                'Table (2)'[Customer] = MAX ( 'Table (2)'[Customer] )
                    && MONTH ( 'Table (2)'[Order Date] )
                        = MONTH ( TODAY () ) + 4
            )
        ) + 0,
    6,
        COUNTROWS (
            FILTER (
                'Table (2)',
                'Table (2)'[Customer] = MAX ( 'Table (2)'[Customer] )
                    && MONTH ( 'Table (2)'[Order Date] )
                        = MONTH ( TODAY () ) + 5
            )
        ) + 0,
    7,
        COUNTROWS (
            FILTER (
                'Table (2)',
                'Table (2)'[Customer] = MAX ( 'Table (2)'[Customer] )
                    && MONTH ( 'Table (2)'[Order Date] ) > MONTH ( TODAY () )
                    && MONTH ( 'Table (2)'[Order Date] )
                        < MONTH ( TODAY () ) + 6
            )
        ) + 0,
    8,
        FORMAT (
            DIVIDE (
                COUNTROWS (
                    FILTER (
                        'Table (2)',
                        'Table (2)'[Customer] = MAX ( 'Table (2)'[Customer] )
                            && MONTH ( 'Table (2)'[Order Date] ) > MONTH ( TODAY () )
                            && MONTH ( 'Table (2)'[Order Date] )
                                < MONTH ( TODAY () ) + 6
                    )
                ),
                COUNTROWS (
                    FILTER ( 'Table (2)', 'Table (2)'[Customer] = MAX ( 'Table (2)'[Customer] ) )
                )
            ),
            "Percent"
        )
)

Put Customer in Rows, rank column and Month in Columns, Value measure in Values, get the result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.