topic Re: How to get the month with the highest number? in DAX Commands and Tips

How to get the month with the highest number?

Anonymous — Wed, 09 Aug 2023 08:40:36 GMT

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

Re: How to get the month with the highest number?

eliasayyy — Wed, 09 Aug 2023 09:00:30 GMT

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

Re: How to get the month with the highest number?

Anonymous — Wed, 09 Aug 2023 11:22:25 GMT

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

Re: How to get the month with the highest number?

Anonymous — Wed, 16 Aug 2023 08:20:54 GMT

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