top 6 month sales by client

Sofinobi — Mon, 02 Oct 2023 14:54:34 GMT

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

Re: top 6 month sales by client

Anonymous — Wed, 04 Oct 2023 09:09:15 GMT

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.

topic top 6 month sales by client in DAX Commands and Tips

top 6 month sales by client

Re: top 6 month sales by client