Multiple Sort Month Columns based on Slier selection

[Anonymous]

Hi All,
I have a very interesting request from client, we have a Financial Month and Client columns as the slicer drop down list, Now the client wants to sort the financial Month based on the client selection.
we have more then 30+ clients and every client have their different financial Start Month as seen in the below screenshot.
For Example: As seen in the Screenshot if the user select Client 1 the Month should start from Mar likewise Client 2 is selected the Month should start Jun etc. I want to create a dynamic column to sort the financial month.
Any Help would be appreciated. Thanks
Regards
Suhel

[v-frfei-msft]

Hi @Anonymous ,

Please check the following steps as below.

1. Create a calculated column as below.

monno = 
SWITCH (
    Table1[Fin Month],
    "Jan", 1,
    "Feb", 2,
    "Mar", 3,
    "Apr", 4,
    "May", 5,
    "Jun", 6,
    "Jul", 7,
    "Aug", 8,
    "Sep", 9,
    "Oct", 10,
    "Nov", 11,
    "Dec", 12
)

2. Create a measure to get the excepted result.

Measure = 
VAR sel =
    SELECTEDVALUE ( 'Table'[Client 1] )
VAR a =
    CALCULATE (
        MAX ( Table1[monno] ),
        FILTER (
            ALL ( Table1 ),
            Table1[Client 1] = sel
                || Table1[Client 2] = sel
                || Table1[Client 3] = sel
                || Table1[Client 4] = sel
                || Table1[Client 5] = sel
        )
    )
RETURN
    IF ( MAX ( Table1[monno] ) >= a, 1, 0 )

[Anonymous · Accepted solution]

@v-frfei-msft 

Thnak you for the soultion