How to get the month with the highest number?

[PowerRon]

Hi, 

I don't know how to solve, maybe someone can help.
This is a part from my FAC_Fraud_Schade table

And this is the measure I try to create. I want to look 13 months back and from those 13 months I want the highest month value.
So if for the month of September the SUM(Aan klant vergoed) is 50.000 and also the highest of all months, I want the measure to return 50.000.
Actually I want the month with the highest value of 'Aan klant vergoed' - 'Voor klant verhaald'.
But already working with one measure gives me trouble.

HighestSalesMonth =

VAR EndDate = MAX('FAC_Fraud_Schade'[Datum])  

VAR StartDate = DATE(YEAR(EndDate) - 1, MONTH(EndDate), DAY(EndDate))  

VAR SalesTable = FILTER('FAC_Fraud_Schade','FAC_Fraud_Schade'[Datum] >= StartDate && 'FAC_Fraud_Schade'[Datum] <= EndDate)

VAR SalesPerMonth =

    SUMMARIZE(SalesTable, 'Date'[Year], 'Date'[Month Name Short],

              "MaxSalesPerMonth", SUM('FAC_Fraud_Schade'[Aan klant vergoed]))  

--VAR MaxSales = MAXX(SalesPerMonth, [MaxSalesPerMonth])

VAR MaxSales = CALCULATE(MAX(SalesPerMonth'MaxSalesPerMonth', ALL(SalesPerMonth)                          

RETURN

    MaxSales

It gives the error here

PS I want the highest value, so that I can give, by using conditonal formatting, the highest value a color. So if SUM Klant Vergoed is the measure, I want in additional column the value 3.712.000 , being the highest month value of the last 13 months in my dataset


I hope someone can help

regards
Ron

@eliasayyy @Jihwan_Kim 

[PowerRon]

Thnx @eliasayyy 
I got it working. Understand what you are doing, but still fin dit hard to find out myself.
The issue her of course is also that you don't want the MAX of the whole dataset, but just the MAX of the last 13 months, while the dataset contains 4 years of data.
Thnx for helping me out

[PowerRon]

Hi @eliasayyy thnx for the great effort. Somewhere this week, hopefully asap, I will test it on my dataset and then let you know.

[eliasayyy · Accepted solution]

hmm this look tricky i made a sample dataset

and a caleendar tabel

first i created the measure for per month - year 

Total Amount by month = 

    CALCULATE(SUM('Table'[Amount]),ALLEXCEPT('Date','Date'[Month - Year]))

next i made a measure to find the past 13 months 

Locate past 13 month = 
VAR _min = EOMONTH(CALCULATE(MAX('Date'[Date]),ALL('Date')),-13)
VAR _max = CALCULATE(MAX('Date'[Date]),ALL('Date'))
RETURN
if( MIN('Date'[Date]) >=_min && MAX('Date'[Date]) <=_max , 1 , 0)

next i made a measure to find the maxx inside this window

Max Amount = 
VAR _t = 
SUMMARIZE(
    FILTER(
        'Date',[Locate past 13 month] = 1),'Date'[Month - Year],"Amount",[Total Amount by month])
RETURN
MAXX(_t,[Amount])

and if you want the columsn to show last results just use 

MAX Amount = 
VAR MaxValue =
    CALCULATE(
        MAXX(
            FILTER(
                ALL('Table'),
                'Table'[Locate past 13 month] = 1
            ),
            [Total Amount by month]
        )
    )
RETURN
    MaxValue

final result