calculate function not giving correct answer

If you just want to lookup the value for the max RefNum do you need to a max of the CurrentMonth column or could you just use the LookupValue function?

MaxMonth = LookupValue(,'Fact Table', 'Fact Table'[CurrentMonth]), max('Fact Table'[RefNum]))

Or if you have mulitple rows with the same RefNum you might need to do something like the following:

MaxMonth =
VAR _maxRefNum = max('Fact Table'[RefNum])
RETURN CALCULATE( MAX( 'Fact Table'[CurrentMonth]), 'Fact Table'[RefNum] = _maxRefNum )

Hi Anonymous ,

You could display the returned result of below measure in a card visual.

MonNo for max refNo =
VAR Curr_Date =
    TODAY ()
VAR Last_day_of_currMon =
    EOMONTH ( Curr_Date, 0 )
RETURN
    IF (
        Curr_Date < Last_day_of_currMon,
        MONTH ( EOMONTH ( Curr_Date, -1 ) ),
        MONTH ( Last_day_of_currMon )
    )

Best regards,

Yuliana Gu