max table filter not working

[Mmahachi]

In my calendar table I have 2 columns other than my key date field one is to say 'yes' or 'no' depending on if the period has been closed by accounting and the second to show a user friendly display of the fiscal period. 

I have tried several formuals but most recently this simple one is providing incorrect data and I am not sure why

Last Period Closed = var LastClosed = CALCULATE(MAX('Calendar'[Fiscal Period Display]), FILTER(ALLEXCEPT('Calendar','Calendar'[AccountPeriodClosed]),MAX('Calendar'[AccountPeriodClosed])="Yes"))

RETURN

LastClosed

This returns a value of the very last fiscal period (FY30 Sep) in the table which is some 10 years in the future and therefore marked as "no" on the account closed period. 

Why isn't the AccountPeriodClosed filter working in this formula?

Ultimately I need to override some outside page filters so that I can view all the periods and then pick the last one where AccountPeriodClosed = Yes

[amitchandak]

@Mmahachi , I need some data to suggest. Try

Last Period Closed =
var _LastClosed = maxx(filter(allselected('Calendar'),'Calendar'[AccountPeriodClosed]="Yes"),[AccountPeriodClosed])

return
CALCULATE(MAX('Calendar'[Fiscal Period Display]), FILTER('Calendar','Calendar'[AccountPeriodClosed] =_LastClosed ))

or

Last Period Closed =
var _LastClosed = maxx(filter(allselected('Calendar'),'Calendar'[AccountPeriodClosed]="Yes"),[AccountPeriodClosed])

return
CALCULATE(MAX('Calendar'[Fiscal Period Display]), FILTER(allselected('Calendar'),'Calendar'[AccountPeriodClosed] =_LastClosed ))

[harshnathani]

Hi @Mmahachi ,

Try this measure.

Last Period Closed =
VAR LastClosed =
    CALCULATE (
        MAX ( 'Calendar'[Fiscal Period Display] ),
        FILTER (
            'Calendar',
            'Calendar'[AccountPeriodClosed]
        ) = "Yes"
    )
RETURN
    LastClosed

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[AntrikshSharma]

@harshnathani  you might want to keep the "yes" inside parenthesis 😄