How to refer to the "highest" month name in a list of month names?

[Anonymous]

Good morning everyone!
Im developing a report for my directors, and I need help with creating a variable.

I have a dropdown list in my report, where 1 or more months names can be selected, the sheet is also filtered by years and category.

Say I selected January, February, Mars and April from the list, I will have acumulated numbers for all these months, but I need this one variable referring only to the last (highest) month in the list (in this case April, but the list could be "January, Mars, June, November" and I need to extract only November from the list). If all months are selected, or none, I need it to refer to december. So far I have this:

SF R =
var DataSelecionada = ALLSELECTED(dim_Data[Year])
var UnidadeSelecionada = ALLSELECTED('dim_UnidadeDeNegócio'[Unidade de Negócio])
var MesSelecionado = LASTNONBLANK(ALLSELECTED(dim_Data[Month Name]);1)
return
CALCULATE(SUM('IF5-T02'[Real])/1000;
FILTER(ALL('IF5-T02');'IF5-T02'[Ano] in DataSelecionada && 'IF5-T02'[Unidade de Negócio] in UnidadeSelecionada && 'IF5-T02'[Nome do Mês] = MesSelecionado && 'IF5-T02'[Fonte] = "FC" && 'IF5-T02'[Contas] = "Saldo Final"))

The problem occurs in this line: var MesSelecionado = LASTNONBLANK(ALLSELECTED(dim_Data[Month Name]);1)

With the ALLSELECTED function, I will get this as an example "January, February, Mars and April", but how do i refer to the highest month in this list, and grab the name? So when I filter my table, I can filter only the month value I need. Using LASTNONBLANK isnt working for what I need.

Thanks in advance to all those who read this!

[v-kelly-msft]

Hi @Anonymous ，

Convert your column 'dim_Data[Month Name] to whole number type.

Then modify your measure as below:

var MesSelecionado =IF( LASTNONBLANK(ALLSELECTED(dim_Data[Month Name]),1)<>1,MAXX('dim_Data',[Month Name],BLANK())

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

@amitchandak Thanks for your answer, but it doesnt work for me. Date isn't part of the selection filter. My sheet only has 3 filters - Category - Year - Month Name, date is not selectable. My Year and Month name are extracted from a date column.
@Greg_Deckler I do have a month number, in the same line as dates and month names, but if 2 years are selected, 2 lines with the same Month Name and Number would be selected, and I always need for the value related tot he 'highest' date selected.
@Ashish_Mathur I dont need to format the selected month, it is already formated as a name, and I cant find max Date, cause Date isn't selectable.
@v-kelly-msft I annexed my Date dimension, when I change "Nome do Mês" column, it causes all lines to become Errors.

[amitchandak]

@Anonymous ,

Can you share sample data in text format and how you shown in dashboard

[Anonymous]

@amitchandak  This is my dashboard, its a matrix. In the Data dimension I shared befored, I extract the info I need from the main Date column. My filters in the dashboard aren't related to the Date Hierarchy, they are related to the columns I created with each category (Month No, Month Name, Year, Quarter... etc). In this example, when I select a month from the "Escolha os Meses" filter, I need my measure to always refer to the highest month selected
I just tried the following syntax, but it isnt working either. I think im having too much problem with this case, I imagine it has a simple solution but I just cant find it

var MaxMes = FORMAT(CALCULATE(MAXX(FILTER(ALL(dim_Data); dim_Data[Nome do Mês] in MesSelecionado && dim_Data[Ano] in DataSelecionada);dim_Data[Mês])); "MMMM")

[Anonymous]

I've managed to make some progress, but still not working.

With the following line, I can a table with all numbers of selected months:

SUMMARIZE(FILTER(dim_Data;dim_Data[Nome do Mês] in MesSelecionado && dim_Data[Ano] inDataSelecionada);dim_Data[Mês])

This returns a single column, with numbers in every line, corresponding to a month (1 = january, 2= february, etc)

Then, I try to use MAX(code in bold) to get the max number in that column, but it doesnt work, since it only accepts columns as references... I don't understand why it won't work, if the code is returning a table with a single column, it should accept the argument.
Does anybody knows how can I make this work?

[v-kelly-msft]

Hi @Anonymous ，

Correct your measure as below:

var MesSelecionado = CALCULATE(MAX(dim_Data[Month Name]),FILTER(dim_Data,dim_Data[Month No]=MAX(dim_Data[Month No])))

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!

[Anonymous · Accepted solution]

@v-kelly-msft and all others who tried to help, I've managed to reach the formula I was looking for, and I didn't tried Kellys specifc method, but it looks a little like what I've wrote.

var MaxMes = FORMAT(CALCULATE(MAXA(dim_Data[Data]); SUMMARIZE(FILTER(dim_Data;dim_Data[Nome do Mês] in MesSelecionado && dim_Data[Ano] in DataSelecionada);dim_Data[Data])); "MMMM")

This works just fine, thanks for everyone who tried to help!

[Ashish_Mathur]

Hi,

Try this measure

=FORMAT(MAX(dim_date[Date]),"mmmm")

Hope this helps.

[Greg_Deckler]

If you have a Date or Month Number column you should be able to do this. Essentially find the maxium of that and then "lookup" the corresponding name. But would need to better undertand your data. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[amitchandak]

@Anonymous 

Assuming Month is part of date table. Means Date is also selected internally

Measure =

var _max = maxx(allselected(Date),Date[Date])

var _mm = maxx(filter(Date,Date[Date]=_max ),Date[Month])

Return

Calculate ([Measure], Date[Month]=_mm) //Or any condition as per need