How to find best month sales and month for each agent

[gauravnarchal]

Hello - How can I find which month was the best month (MMM-YY) and (Total) sales for each agent?

Total sales = Sale - Marketing Fee

Click here to download the PBIX file. Click Here 

I would like the result to be shown as below

Thanks

[v-cgao-msft · Accepted solution]

Hi @gauravnarchal ,

The calendar needs to contain all the dates in the fact table:

Calendar2 = 
ADDCOLUMNS (
    CALENDAR ( MIN ( 'Table 1'[InvoiceDate] ), MAX ( 'Table 1'[InvoiceDate] ) ),
    "MonthYear", FORMAT ( [Date], "mmmm yyyy" )
)

Relationships:

Total sales:

Total sales = 
SUM('Table 2'[Sale]) - SUM('Table 2'[Marketing Fee])

Then new a measure for filter:

Filter = 
VAR _agent =
    MAX ( 'Table 1'[Agent] )
VAR _monthyear =
    MAX ( 'Calendar2'[MonthYear] )
VAR _max =
    MAXX (
        FILTER (
            SUMMARIZE (
                ALLSELECTED ( 'Table 1' ),
                'Table 1'[Agent],
                'Calendar2'[MonthYear]
            ),
            'Table 1'[Agent] = _agent
        ),
        [Total sales]
    )
VAR _filter = IF([Total sales]<>BLANK()&&[Total sales]<>_max,0,1)
RETURN
    _filter

Result:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data