cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
powerbacc
Frequent Visitor

max in mesure over year equal to current month

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? 🙂
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: max in mesure over year equal to current month

Hi @powerbacc ,

 

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,

 

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

View solution in original post

3 REPLIES 3
Super User IV
Super User IV

Re: max in mesure over year equal to current month

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

 

 





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin


powerbacc
Frequent Visitor

Re: max in mesure over year equal to current month

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?

 

 

Highlighted
Community Support
Community Support

Re: max in mesure over year equal to current month

Hi @powerbacc ,

 

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,

 

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

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors