max in mesure over year equal to current month

[Anonymous]

hello,

What I try to do is, get the highest value of all month equal to the current month over all the years. 

2020 jan 11

2019 jan 5

2018 jan 8

2017 jan 13

2016 jan 5

In this case I would expect 13 as an output.

What I curenty have, will output in the same way as the table above:

highest =

var monthh = CALCULATE(count('Rol'[enddate]);MONTH(TODAY())=MONTH('Rol'[enddate]))

return

monthh

I've tried many diffrent things, either resulting in an error or a wrong value. 

Does anyone know how to achieve this within a single messure? 🙂

[amitchandak]

You need the max day, then why are you are doing count, did I got correctly?

var max_day = CALCULATE(maxx('Rol',day('Rol'[enddate]));all('Rol'[enddate]);MONTH(TODAY())=MONTH('Rol'[enddate]))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[Anonymous]

hey, thanks for your reply! 

The output is not what I expected 

enddate is the date a subscrition ends, so I want to count how many end each month. 

Then I want to know what the highest amount of enddates fell in January (the current month is jan), In this case I would expect 84 as an output:

2020 jan 12

2019 jan 43

2018 jan 16

2017 jan 84

2016 jan 36

I hope I made it a bit clearer?

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can try to use the following measure to meet your requirement:

Highest =
MAXX (
    SUMMARIZE (
        'Rol',
        'Rol'[enddate].[Year],
        "C", CALCULATE (
            COUNT ( 'Rol'[enddate] ),
            MONTH ( TODAY () ) = MONTH ( 'Rol'[enddate] )
        )
    ),
    [C]
)

Best regards,