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

 

 

DateNumberAlarmsMaxPerMonth
01-01-20162030
02-01-20162130
03-01-20162030
04-01-20163030
05-01-20161530
06-01-2016130
07-01-2016230
08-01-2016330
09-01-2016530
10-01-2016630
20-02-20165860
21-02-20166060
22-02-2016260
23-02-2016360
24-02-2016160
25-02-2016560
15-03-201615
16-03-201655
17-03-201615
18-03-20162

5

 

 

 

 

 

 

 

Thanks in advance for your help.

 

Mariana

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

1 Reply

  • 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