How to calculate Max of a measure for each month

[Anonymous]

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])))

Thank you ! 

[v-yalanwu-msft]

Hi, @Anonymous ;

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]

Hi, @Anonymous ;

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]

@Anonymous 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.⚡

[parry2k]

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 ?

[Anonymous]

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

[parry2k]

@Anonymous 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.⚡

[Anonymous]

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.