MAX in FILTER context confusion

[Jacon25]

Hello,

It is likely my misunderstanding of MEASURE being used in a FILTER context, but I am struggling with this formula. I am attempting to count rows in a table filtered by the day before max date in that table.

VAR maxDate = CALCULATE(MAX(Table1[Date]), ALLEXCEPT(Table1, Table1[Date])) - 1
COUNTROWS(FILTER(ALL(Table1), Table1[Date] = maxDate))

The formula returns blank even though I have confirmed there are rows in that table for that specific date.

What's odd is if I use a DATEVAL with the day before the max date manually entered in place of "maxDate", the formula works and I get the correct row count.

Appreciate the help!

[VasTg]

@Jacon25 

Try this..

Measure 4 = VAR A = MAX(Table1[Date])-1
RETURN COUNTROWS(FILTER(ALL(Table1),Table1[Date]=A))

If it helps, mark it as a solution

Kudos are nice too

[amitchandak]

Try like

VAR maxDate = MAXX(Table1,Table1[Date]) - 1

calculate(COUNTROWS(Table1),FILTER(ALL(Table1), Table1[Date] = maxDate))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

[Jacon25]

Hi @amitchandak ,

Thanks for your post. Perhaps I should have explained more. I would need the ALLEXCEPT included to ensure that some other filters are ignored when calculating MAX. If I use your formula, it will produce a result, however the value is not correct since it's taking into account the other filters on Table1.

[Jacon25 · Accepted solution]

Seems like I have it working now. I had all of the right pieces. Here is what worked for me:

VAR maxDate = CALCULATE(MAX(Table1[Date]), ALLEXCEPT(Table1, Table1[Date])) - 1
VAR potentialTimeSingleDay = COUNTROWS(FILTER(Table1, Table1[Date] = maxDate))