All Time "Best Month" Production

[SWilson_GS]

Hello! I'm struggling trying to present a "top month" view for production for all time. i.e. one producer's best month may have been April 2018 while another's best production month may have been September 2020. I'm using 

BestMonth =

if(HASONEVALUE(Table[LoanOfficerName]),MAXX(SUMMARIZE('Date',[Year],'Date'[Monthnumber],"ABCD",[$TotalSales]),[ABCD]),BLANK())

And this displays as expected. However, now I'm trying to also display the month/year next to that measure in the same table. I've been playing around with the same measure to display the month/year but failing. Any help?

Thanks in advance!

[mahoneypat · Accepted solution]

Please try this measure expression.  It returns both the max amount and the date it occurred on.  If there is a tie, it will concatenate them together.

Best Month =
VAR vSummary =
    ADDCOLUMNS (
        SUMMARIZE (
            'Table',
            'Table'[CloseDate],
            'Table'[LoanOfficerName]
        ),
        "cProduction",
            CALCULATE (
                SUM ( 'Table'[Production] )
            )
    )
VAR vMaxProd =
    CONCATENATEX (
        TOPN (
            1,
            vSummary,
            [cProduction], DESC
        ),
        'Table'[CloseDate] & " - "
            & FORMAT (
                [cProduction],
                "Currency"
            ),
        "; "
    )
RETURN
    vMaxProd

Regards,

Pat

[SWilson_GS]

Thank you! This solution worked best for my purposes.

[littlemojopuppy]

Can you provide some sample data to work with?

[SWilson_GS]

Here's an example. CloseDate is joined on a date table. 

In this last view I'd like "MonthName" to display "2019 - Dec" for Lender 1, "2019 - Dec" for Lender 2, and "2020 - Jan" for Lender 3. 

Please let me know if that doesn't make sense, thanks!

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[littlemojopuppy]

Hi!  I came up with this...

Best Production Month:=VAR	MonthlySummary =
		GROUPBY(
			Production,
			Production[LoanOfficer],
			Production[CloseDate],
			"MonthlyProduction",
			SUMX(
				CURRENTGROUP(),
				Production[Production]
			)
		)
	VAR	TopPerformingMonths =
		TOPN(
			1,
			MonthlySummary,
			[MonthlyProduction],
			DESC
		)
	RETURN

	CALCULATE(
		FIRSTNONBLANK(
			Production[CloseDate],
			TRUE()
		),
		TopPerformingMonths
	)