top 6 month sales by client

[Sofinobi]

hello,

i have a measure to calculate the total sales
[total Sales]
please, i want to calculate the top 6 sales by month by client ( start date from 06/2022 to 09/2023)

(the best 6 sales by month for each client)

thank you

[v-rzhou-msft · Accepted solution]

Hi @Sofinobi ,

I suggest you to create a Calendar table which is connected to your data table to help your calculation.

Calendar = ADDCOLUMNS(CALENDARAUTO(),"YearMonth",YEAR([Date]) * 100 +MONTH([Date]),"Month/Year",COMBINEVALUES("/",FORMAT(MONTH([Date]),"00"),YEAR([Date])))

Then create a rank measure.

Rank = 
VAR _SUMMARIZE =
    SUMMARIZE (
        ADDCOLUMNS ( ALL ( 'Table' ), "Month/Year", RELATED ( 'Calendar'[Month/Year] ) ),
        [Month/Year],
        [Customer],
        "Sum",
            SUMX (
                ADDCOLUMNS ( 'Table', "Month/Year", RELATED ( 'Calendar'[Month/Year] ) ),
                [Sales]
            )
    )
VAR _ADDRANK =
    ADDCOLUMNS (
        _SUMMARIZE,
        "RANKX",
            RANKX (
                FILTER ( _SUMMARIZE, [Month/Year] = EARLIER ( [Month/Year] ) ),
                [Sum],
                ,
                DESC,
                DENSE
            )
    )
RETURN
    SUMX (
        FILTER (
            _ADDRANK,
            [Month/Year] = MAX ( 'Calendar'[Month/Year] )
                && [Customer] = MAX ( 'Table'[Customer] )
        ),
        [RANKX]
    )

Add this measure into visual level filter and set it to show items when value <=6.

Best Regards,
Rico Zhou

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