Nested Filter? Trying to find amount for last non-zero date

I’m trying to develop a Cash Status dashboard.

I’ve put together a simple model which shows my problem.

I want to show the value of the last BAI code that = 45 for Acct =1.

The problem is that the last BAI code 45 for Acct 1 transaction took place on 1/2/2020, but the last date in the calendar table (not code 45) takes place on 1/3/2020. As a result the measure is returning blank, because the last date in the calendar table is 1/3/2020, but there aren’t any BAI code 45 transactions for Acct 1 on that day.

My measure is
Last Acct 1 BAI 45 = CALCULATE([Sum of Acct 1 BAI 45 Amount],FILTER(‘Calendar’,‘Calendar’[DayID]=MAX(‘Calendar’[DayID])))

I’m not sure how to get the correct coding. In my example the correct answer = 20.

I’m new to DAX, but my thinking was create a table that is filtered to only Acct =1,and BAI =45. Then filter that table down to the last date and return the resulting amount[.

To me that sounds like nesting FILTER functions, but when I tried, it didn’t seem to work. Also, I’m not sure if nesting FILTER functions is really the way to go.

Let me know if you need more clarification on the problem.

Test on Last Amount for Product B.pbix|attachment](upload://faq9RLLOKUwdwcAmeMH3pWIEyDz.pbix) (53.1 KB) Test Raw Data

Hello @BillK,

Thank you for posting your query onto the Forum.

I’m attaching the working of my file for the reference.

If you’re following the New Date table code which was recently updated then in that case you can filter it out and have the actual value instead of blank.

You can try out this formula and check if it works for you -

Last Acct 1 BAI 45 = 
IF( LASTDATE( Calendar[Date] ) > TODAY() , 
               CALCULATE([Sum of Acct 1 BAI 45 Amount],
                      FILTER(‘Calendar’,‘Calendar’[DayID] <= MAX(‘Calendar’[DayID])))`

Please Note: The naming convention might differ in your file so you can use it accordingly.

I’m also providing a link where similar sort of problem was faced by one of the member and got solved.

Hoping you find this useful and helps you in your analysis.

Thanks & Warm Regards,

MTD Measure showing Blank

eDNA Forum - Max Date Filter Solution.pbix (128.9 KB)


Welcome to the forum - great to have you here!

Apparently, all three of us jumped on this one at the same time, so you get 3 solutions for the price of one today. :smiley: I always think it’s helpful to see different ways of achieving the same result, so in that spirit, here’s my measure:

Filtered Value = 

VAR MaxDate =
    MAX( Data[Date] ),
        Data[Account] = 1 &&
        Data[BAI Code] = 45

VAR Result =
    MAX( Data[Amount] ),
        Data[Account] = 1 &&
        Data[BAI Code] = 45 &&
        Data[Date] = MaxDate


Hope this is helpful.

  • Brian

eDNA Forum - Max Date Filter Solution.pbix (118.8 KB)

Hi @BillK. Sorry, got cut off a couple of hours ago in mid post and had to withdraw; I didn’t get a chance to finish my solution until a few minutes ago. Min uses 2 measures as well

Max Date per Account and Code = 
VAR CurrentAccount = SELECTEDVALUE( Transactions[Account] )
VAR CurrentCode = SELECTEDVALUE( Transactions[BAI Code] )
    FILTER( ALL( Transactions ),
        Transactions[Account] = CurrentAccount &&
        Transactions[BAI Code] = CurrentCode ),
    Transactions[Date] )

Max Amount =
VAR CurrentDate = SELECTEDVALUE( Transactions[Date] )
IF( CurrentDate = [Max Date per Account and Code], [Total Amount], BLANK() )

You can then display the Last Amount for any account/code combination in a grid, something like:

Hope it helps.
eDNA Forum - Nested Filter - Last Nonzero Date.pbix (23.3 KB)

Hi @BillK, we hope that you are having a great experience using the Support Forum so far. We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!


Thank you so much for the fast response!

I tried Brian’s solution first and it worked .

I really appreciate the help.


Hello @BillK !!

Greta news that @BrianJ was able to help you with your inquiry.

If you want to learn more about Last Non Zero date, I would recommend you to watch the “Time Intelligence Calculation Module”

I think this video would also come in handy for you in the future.

Let me know if this works for you.