topic max table filter not working in DAX Commands and Tips

max table filter not working

Mmahachi — Tue, 18 Aug 2020 12:35:59 GMT

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

Re: max table filter not working

Anonymous — Tue, 18 Aug 2020 12:51:34 GMT

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)

Re: max table filter not working

amitchandak — Tue, 18 Aug 2020 13:15:36 GMT

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

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

Re: max table filter not working

AntrikshSharma — Tue, 18 Aug 2020 15:18:46 GMT

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