Filter data by Last Modified Date Slicer

[CoryMBush]

Hi,

My data source will be adding new rows each time a specific line item is modified.  (see table exable).
I need to set my date slicer (Report run Date) as a Max date but then only look at the most recent data Per Inv ID.

For Example, If i set my Date slicer to 10/4, I would only see rows 3,4,5.  If i set the slicer to 10/7, i would only see rows 4,6,7.

I assume I need to use DAX variables or something to achieve this but my DAX isn't very strong.

[kentyler]

I used a measure to determine whether each index was the most recent for that invoice id
Most Recent =
VAR current_date =
    SELECTEDVALUE ( Invoices[date] )
VAR invoice_id =
    SELECTEDVALUE ( Invoices[invoice id] )
VAR max_date =
    CALCULATE (
        MAX ( Invoices[date] ),
        ALL ( Invoices ),
        Invoices[invoice id] = invoice_id
    )
VAR is_latest = current_date = max_date
RETURN
    is_latest

it stores the date and index id of the row that is in the filter context and then looks up the most recent invoice date with that id.

If that date and the current invoice date are the same, that is the most recent invoice.
Then I created a calculated table to filter out all but the most recent invoices using this measure

Recent Invoices = filter(invoices,[Most Recent])

Which returns only the rows which are the most recent version of the invoice

if you attach your slicer to that table you should get the results you're looking for.

[CoryMBush]

I got the "Most Recent" measure to tie out to my slicer and work accurately by switching the All(  to Allselected( in the Max_Date Variable.  but the Calculated Table still only shows the Max Modified Date per invoice instead of the rows where Most Recent = "True"

[kentyler]

Things can get complicated with all the forces acting on a value in DAX. Would you like to do a screen share and take a look at what you have together. Send me an email at ken@8thfold.com and a good date and time and I'll start an online meeting and sent you an invitation.

[CoryMBush]

Hi @kentyler ,

Thanks for your response!  This seems really close to what i'm looking for but not quite.  The solution provided is only showing me the most recent row per invoice overall but what i need is the most recent invoice that is less than the Date i've selected with my slicer.  

for example, If i have an invoice with 3 different modified dates(11/1, 11/3, 11/6), I need it to show me the most recent row that is also less than the date I've selected with my slicer/date slider.

• If i select 11/2 with my date slicer, it will eliminate any data with a mod date that is greater than 11/2 but also only return the most recent of what is remaining.  In this case it would show 11/1
• If i select 11/4 with my date slicer, it would return the 11/3 row.

I tried copying your code and replacing your table/column names with the ones from mine and it doesn't provide the right results.