cancel
Showing results for
Did you mean:
Frequent Visitor

## How to calculate Max of a measure for each month

Hi,

I want to display the Max for each month for the negative values for available stocks in the table as shown on the photo. Available stocks is a measure. Example for January , i should see 19 for -20,-5....

I have tried with MAXX function which works for getting max from a measures, but it gives me the max for all months, but I need the max per month.

Available Stocks =
VAR A=[Stocks]-[Wrong stocks]
VAR C=MAXX(ALL('Date Table'),[Stocks]]-[Wrong stocks])
var B= IF(A<=0,C,A)
RETURN B

I also try to filter by month so i create calculated column, but it doesn not give me desired result yet. Any help appreciated. 🙂
YearMonthnumber=FORMAT ( [Date], "YYYY/MM" )
max per month = CALCULATE([Stocks]-[Wrong stocks],FILTER('Date Table','Date Table'[YearMonthnumber]=EARLIER('Date Table'[YearMonthnumber])))

Thank you !
7 REPLIES 7
Community Support

Hi, @AnaStone_10 ;

Is your problem solved?? If so, Would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support

Hi, @AnaStone_10 ;

You could try to modify the measure as below:

``````Available Stocks =
VAR A = [Stocks] - [Wrong stocks]
VAR C =
MAXX (
ALLEXCEPT ( 'Date Table', 'Date Table'[YearMonthnumber] ),
[Stocks]]-[Wrong stocks])
VAR B = IF ( A <= 0, C, A )
RETURN B
``````

If it not right ,can you share a simple data or more detail about your table?

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Super User

@AnaStone_10 try this measure:

``Max Sales = MAXX ( ALL ( 'Date Table'[Date] ), CALCULATE( [Available Stock Measure], VALUES ( 'Date Table'[Year Month] ) ) )``

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Super User

What's the point of showing dates when you are getting max by month, each date will have the same value for the month? Not sure what you are trying to achieve ?

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Frequent Visitor

Yes, the same value! Its a part of an analytics scenario.

Super User

@AnaStone_10 not sure why it is so complicated, assuming your data table is connected to your stock table and date table has a year/month (which seems you already have)

``Max Stock for the Month = MAXX ( VALUES ( DateTable[YearMonth] ), [Your Stock Measure] )``

In a table visual, put YearMonth from Date table and the above measure and it should work.

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Frequent Visitor

Yes,  data table is connected to y stock table and date table has a year/month (tables are properly connected). I want to use date column from Date table(not Year Month) and this measure does not give me desired result, its still diplays negative numbers.

Announcements

#### Launching new user group features

Learn how to create your own user groups today!