Forum Discussion

Sea_and_Anne's avatar
Sea_and_Anne
Helper I
9 years ago
Solved

Column- Max per month

Hello,   Hello, I'm trying to add a column "MaxPerMonth" where i have the value max of the numberAlarms per month. I've tried to use the function MAXX but i didn't get the required result, can yo...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi Sea_and_Anne,

    Firstly, create a new column named yearmonth using the following formula.

    yearmonth = FORMAT(Table[Date],"yyyy/mm")


    Secondly, create a new column named Maxpermonth using the following formula.

    Maxpermonth = CALCULATE(MAX(Table[NumberAlarms]),FILTER(Table,Table[yearmonth]=EARLIER(Table[yearmonth])))

    You will get your desired result as shown in the following screenshot. For more details, please review the example in the attached PBIX file.


    Thanks,
    Lydia Zhang

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi Sea_and_Anne,

Firstly, create a new column named yearmonth using the following formula.

yearmonth = FORMAT(Table[Date],"yyyy/mm")


Secondly, create a new column named Maxpermonth using the following formula.

Maxpermonth = CALCULATE(MAX(Table[NumberAlarms]),FILTER(Table,Table[yearmonth]=EARLIER(Table[yearmonth])))

You will get your desired result as shown in the following screenshot. For more details, please review the example in the attached PBIX file.


Thanks,
Lydia Zhang