cancel
Showing results for
Did you mean:
Helper I

## 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 you please help me?

 Date NumberAlarms MaxPerMonth 01-01-2016 20 30 02-01-2016 21 30 03-01-2016 20 30 04-01-2016 30 30 05-01-2016 15 30 06-01-2016 1 30 07-01-2016 2 30 08-01-2016 3 30 09-01-2016 5 30 10-01-2016 6 30 20-02-2016 58 60 21-02-2016 60 60 22-02-2016 2 60 23-02-2016 3 60 24-02-2016 1 60 25-02-2016 5 60 15-03-2016 1 5 16-03-2016 5 5 17-03-2016 1 5 18-03-2016 2 5

Mariana

1 ACCEPTED SOLUTION
Microsoft

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Microsoft

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors