Last entry in a filtered selection

[timelez]

Hi Everybody, i cant find a solution to a query that i have to do for the report of my company.

The data is based on a excel file where some formulas and the date is already entered in advance. my colleagues only enter some values day by day. 

I now need to know the last entered value of column that has been filtered by a slicer.

For example this table:

If i set the slicer to 2.9.2020 to 4.9.2020 and use LASTNONBLANK the result for Errors Found is 10 which is obviously not correct. So i have read about the Index function but as you can see, the MAX Index is 4 which is also not the correct value.

Therefore i need a formula which obeys the Date Slicer and shows the Value "3" which in this example ist the true last value in that table.

I cannot delete the additional lines at the end because some of my colleagues might forget to copy the formulas or change them by accident.

Thank you in advance.

Index	Date	Errors Found	Errors Fixed	Working Hours
1	1.9.2020	2	2	100
2	2.9.2020	10	10	200
3	3.9.2020	3	2	300
4	4.9.2020

[v-alq-msft · Accepted solution]

Hi, @timelez 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create a measure as below.

Result = 
CALCULATE(
    MAX('Table'[Index]),
    FILTER(
        ALLSELECTED('Table'),
        NOT(ISBLANK('Table'[Errors Found]))
    )
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@timelez - If I am reading this correctly:

Measure = MAXX(FILTER('Table',NOT(ISBLANK([Errors Found]))),[Index])