Count id based Max Date and status

[Lucianovaz]

Friends, SAVE ME!!!HELP!!! COUNT MAX DATE with criteria.

In a data slicer (year), knowing what was the last status of id_voluntario, if it is "AND" count, but I can't solve it.
My table: tb_volunteers

Where these volunteers come in and out all the time and each time he enters he enters a log line and the status changes to "E",
but the input_date remains the first, and writes update_date at that moment, when he exits he inserts a new log line status changes "Y" and writes the update_date.
Volunteer_id,birth_date, entry_date, status,update_date.
I need to count dynamically according to the movement of the data slicer.
And also average age of these volunteers until the data slicer date.
Also know the average age of the counted volunteers.

[Lucianovaz]

In short, I need to filter the last dates of each volunteer and count if the status is "E" 
and also calculate the age of this volunteer until the selected year or month.
for example if
select the year 2008 the id A101 is inactive because the last date was 05/10/2005 and the status ="S".
already if you select 2020 the same id A101 is with status="E"
Already the id A109 is active because the last date was 03/12/2009 and status ="E".

[v-chenwuz-msft · Accepted solution]

Hi @Lucianovaz ,

You need create an  independent date table for slicer. Then create some smilier measures like the following to calculate.

[if], put this measure in filter pane of table visual to display the last status of id_voluntario and set it show items when is 1.

if =
VAR _selected_date =
    MAX ( 'calendar'[Date] )
VAR _topn =
    TOPN (
        1,
        FILTER (
            ALLSELECTED ( 'Table' ),
            [Id_voluntario] = SELECTEDVALUE ( 'Table'[Id_voluntario] )
                && [data_atulizacao] <= _selected_date
        ),
        [data_atulizacao], DESC
    )
VAR _status_date =
    MAXX ( _topn, [data_atulizacao] )
RETURN
    IF ( SELECTEDVALUE ( 'Table'[data_atulizacao] ) = _status_date, 1 )

Result:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

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