Return Measure for Year and MAX Month

[T_Buck_1027]

I am trying to create a measure that calculates the measure value for a given year and the "max" month of that year.

For example; for 2019 the calculated measure would return the measure value for October 2019.

The formula below works but the problem is that the measure takes a very long time to process.

Is there a better way to get the same result that loads quicker?

I have a single Fact table with a many to one relationship to a large dim table with demographic data.

Measure_2019MaxMonth =

Var MaxMonth = MONTH(MAX(vwMeasureTable[Date]))

RETURN

CALCULATE([Measure],YEAR(vwMeasureTable[Date])=2019 && MONTH(vwMeasureTable[Date])=MaxMonth)

[T_Buck_1027 · Accepted solution]

I figured out a solution that didn't require me to re-write the formula. The issue was with the large Dim Table that I was connected to. I merged the Dim table with the foreign key of my fact table in order to filter the Dim table down to only the rows I needed. I then connected to that filtered down Dim table and the processing time decreased significantly. 

[calerof]

Hi @T_Buck_1027 ,

First I'd suggest you create a Calendar table, there are several ways, but I'm using one I like.

Then I use this easy measure:

MaxDate = LASTDATE( Tabla1[Date] )

 And you get this result:

Hope it helps. If so, please mark it as accepted response.

Kind regards,

Fernando