Ranking most common codes from two columns

[Anonymous]

I need some help with this issue.

I have a SharePoint list containing three columns. First column has an agent name, second has a response code that corresponds with an action the agent needs to take and the third is a location. I want to rank the two most common codes from both columns from the last 30 days. In this example, two most common codes are CHA.1 and LA.1

NAME:              RESPONSE CODE:                LOCATION CODE:

John                  CHA.1.                                 LA.1

Henry.               GH.2.                                   NYC.1

Martha.             CHA.1.                                 LA.1

Ideally I would like to show the two most common codes from the previous month and from the current month to date, if possible.

[Anonymous]

@Greg_Deckler Since you responded to this, the formula has worked great for ranking the most common codes from the previous month. But I am having a hard time incorporating this formula into my dashboard for the purpose of ranking month to date. Can you tell me where I'm going wrong? 

This is what I have:

Measure = 

VAR __EndDate = Today() 

VAR __Start Date = DATE(YEAR(__End Date), Month(__End Date), 1)

VAR __Table = SUMMARIZE('Table1', 'Table1'[RESPONSE CODE], "__Count", COUNTROWS ('Table1')) 

VAR __Table1 = SUMMARIZE('Table1' [LOCATION CODE], "__Count", COUNTROWS('Table1')) 

VAR __MaxLocCount=MAXX(__Table, [__Count]) 

VAR __MaxRepCount=MAXX(__Table, [__Count]) 

RETURN

MAXX(Filter(__Table1, [__Count] = __MaxRepCount), [LOCATION CODE]) & MAXX(Filter(__Table, [__COUNT] = __MaxLocCount), [RESPONSE CODE]) 

[Anonymous]

@Greg_Deckler This does work beautifully.

The only one issue is that I'm trying to rank them according to last month and month to date. Is there something we can add to the formula for that? 

[Greg_Deckler]

@Anonymous What do you have in the way of a Date column? You can get your dates like:

VAR __EndDate = TODAY()
VAR __StartDate = DATE(YEAR(__EndDate),MONTH(__EndDate),1)
// these would be for mtd
VAR __EndDate = EOMONTH(TODAY(),-1)
VAR __StartDate = DATE(YEAR(__EndDate),MONTH(__EndDate),1)
// these would be for previous month

// You can then use these within your SUMMARIZE like this:
SUMMARIZE(FILTER('Table',[Date] >= __StartDate && [Date]<=__EndDate),....)

[Anonymous]

Both of these worked great! Thanks Greg! 

[Greg_Deckler · Accepted solution]

@Anonymous Maybe:

Measure 2 = 
    VAR __Table = SUMMARIZE('Table3',[LOCATION CODE:],"__Count",COUNTROWS('Table3'))
    VAR __Table1 = SUMMARIZE('Table3',[RESPONSE CODE:],"__Count",COUNTROWS('Table3'))
    VAR __MaxLocCount = MAXX(__Table,[__Count])
    VAR __MaxRepCount = MAXX(__Table,[__Count])
RETURN
    MAXX(FILTER(__Table1,[__Count] = __MaxRepCount),[RESPONSE CODE:]) & " and " & MAXX(FILTER(__Table,[__Count] = __MaxLocCount),[LOCATION CODE:])