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

How to calculate Max of a measure for each month

 

Hi,

Please for an advice how to solve this.

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])))
 
available stocks.PNG
 
Thank you ! 
7 REPLIES 7
v-yalanwu-msft
Community Support
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.

v-yalanwu-msft
Community Support
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.

parry2k
Super User
Super User

@AnaStone_10 try this measure:

 

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

 

Follow us on LinkedIn

 

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.






Did I answer your question? Mark my post as a solution.

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





parry2k
Super User
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 ?






Did I answer your question? Mark my post as a solution.

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





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

parry2k
Super User
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)

 

add a simple measure:

 

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.

 

Follow us on LinkedIn

 

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.






Did I answer your question? Mark my post as a solution.

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





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.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.