Max Month Number per Year

[Anonymous]

Hello All,

I'm wanting to return the max monthnumber for each year. For the example below 2018 would return 12 AND 2019 will return 9. I will then be using that calculation to divide a measure to get a average for the year. 

[Anonymous]

1	2018
10	2018
11	2018
12	2018
6	2018
7	2018
5	2018
9	2019
4	2019
1	2019
2	2019
3	2019
5	2019

[nvprasad]

Hey,

Here is two forumulas you can use either.

1.Maxmonth = MAX('Table'[Number])

2.MaxMonth = CALCULATE(MAX('Table'[Number]),FILTER('Table','Table'[Year]=SELECTEDVALUE('Table'[Year])))

Thanks,

Venkata Nalla

[Tahreem24 · Accepted solution]

@Anonymous ,

Create a below meaure to get Max monthnumber.

Max Month Number = CALCULATE(Max(Sheet9[MonthNumber]),ALLEXCEPT(Sheet9,Sheet9[Year]))

Don't forget to hit Thumbs up and accept this as a solution if you find it helpful! 

[Anonymous]

@Tahreem24 

Thank you,

When doing this formula I'm getting 9 for both the years 

[Tahreem24]

@Anonymous ,

No, I am getting the 9 for 2019 and 12 for 2018. Refer below screen shot.

Don't forget to hit Thumbs up and accept this as a solution if you find it helpful! 

So it may helps other!

[Anonymous]

is your monthnumber and year a date field ? 

[Anonymous]

This is my calculation: 

Max Month Number = CALCULATE(MAX(FACT_AreaExpenses_PBuddTable[Month]),ALLEXCEPT(DIM_Calendar,DIM_Calendar[Year]))

The year is coming from another table: 

[Tahreem24]

@Anonymous ,

Both fields are in numeric form(Whole number). You didn't mention about the Year is coming from different table. FOr this make a proper relationship betweeb those table.

Don't forget to hit Thumbs up and accept this as a solution if you find it helpful! 

[Anonymous]

Thanks I changed to a whole number and it worked !

[Tahreem24]

Great @Anonymous !

I will really appreciate your KUDOS/Thumbs up! 😊

Good Luck!