Forum Discussion

Lucianovaz's avatar
Lucianovaz
Frequent Visitor
4 years ago
Solved

Count id based Max Date and status

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_volunte...
Need Help
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    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.

     

Lucianovaz's avatar
Lucianovaz
Frequent Visitor
4 years ago
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's avatar
    v-chenwuz-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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.