Forum Discussion

Mmahachi's avatar
Mmahachi
Icon for Advocate I rankAdvocate I
6 years ago

max table filter not working

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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      AntrikshSharma
      Icon for Community Champion rankCommunity Champion
      6 years ago

      Anonymous  you might want to keep the "yes" inside parenthesis 😄 

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

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