Help with calculating Min/Max values for Chart

[DarrenLau]

Hi,

I am very puzzled by this behaviour and want to seek community members advise. I am trying to determine Min/Max values of a chart using the DAX formula below:-

MaxOppWonValue_MthName = 
MAXX (
    ALLSELECTED ( 'Calendar'[Mth] ),
    [OppWonValue]
)

MinOppWonValue_MthName = 
MINX (
    ALLSELECTED ( 'Calendar'[Mth] ),
    [OppWonValue]
)

where [OppWonValue] is

OppWonValue = 
CALCULATE (
    SUM ( 'Opportunities'[Revenue] ),
    'Opportunities'[Status] = "Won"
)

The issue is when I use the Mth column in Calendar table (eg Jan, Feb, Mar, Apr ...) as the common axis on the chart, the charts do not show the correct Min/Max values.

However, if I change the DAX formulas to MonthNumberOfYear and the chart axis to the MonthNumberOfYear, the calculations work.

MaxOppWonValue_MthNumber = 
MAXX (
    ALLSELECTED ( 'Calendar'[MonthNumberOfCalendarYear] ),
    [OppWonValue]
)

MinOppWonValue_MthNumber = 
MINX (
    ALLSELECTED ( 'Calendar'[MonthNumberOfCalendarYear] ),
    [OppWonValue]
)

What am I doing wrong or missing here?

[DarrenLau]

Wow, thanks for the quick response. I remember reading that particular article and making a mental note for future (which obviously didn't help in this instance hahaha).

End result that I was trying to achieve:

[CNENFRNL · Accepted solution]

An very hidden pitfall of "Sort by Column" setting. You may refer to this article,

Side effects of the Sort By Column setting in DAX - SQLBI

MinOppWonValue_MthName = 
MINX (
    ALLSELECTED ( 'Calendar'[Mth] ),
    CALCULATE([OppWonValue], ALL('Calendar'[MonthNumberOfCalendarYear]))
)