Distinctcount IDs by status and last date updated

[gbernardes]

Hello. I have a table that register status of tasks in the time. 

The problem is: I need to count the tasks open/done using the lastdate selected in the slicer of "date updated"

Eg: when I select slicer the date 10/05/2022 I have this result
status Concluídas = 1
status Abertas = 0
Total = 1

when I select slicer the date 09/05/2022 I have this result
status Concluídas = 0
status Abertas = 1
Total = 1

Now I'm using the following measure, it's ok when I filter only 1 task id. But with all ids not working:

Total Tasks = 
VAR Max_Date= CALCULATE(MAX(fTarefas[Date created]),FILTER(ALLSELECTED(fTarefas),fTarefas[Tarefa - ID]=MAX(fTarefas[Tarefa - ID])))
RETURN
CALCULATE(DISTINCTCOUNT(fTarefas[Tarefa - ID]),FILTER(fTarefas,fTarefas[Date created] = Max_Date)) +0

Tasks Done (concluídas) = 
VAR Max_Date= CALCULATE(MAX(fTarefas[Date created]),FILTER(ALLSELECTED(fTarefas),fTarefas[Tarefa - ID]=MAX(fTarefas[Tarefa - ID])))
RETURN
CALCULATE(DISTINCTCOUNT(fTarefas[Tarefa - ID]), fTarefas[Tarefa - Status] = "Concluídas", FILTER(fTarefas,fTarefas[Date created] = Max_Date)) +0

Taks open (abertas) = 
VAR Max_Date= CALCULATE(MAX(fTarefas[Date created]),FILTER(ALLSELECTED(fTarefas),fTarefas[Tarefa - ID]=MAX(fTarefas[Tarefa - ID])))
RETURN
CALCULATE(DISTINCTCOUNT(fTarefas[Tarefa - ID]),fTarefas[Tarefa - Status] = "ABERTAS", FILTER(fTarefas,fTarefas[Date created] = Max_Date)) + 0

Anyone know what is wrong?

[v-jingzhang · Accepted solution]

Hi @gbernardes 

Can you provide some sample data with multiple task ids and expected result based on that? May a task have multiple status records on a date? Do you only need to consider the last status of a task on a selected date?

Based on my assumptions, you can first add two calculated columns in the table:

Date = DATEVALUE('Table'[Date updated])

Is Last record = 
IF (
    CALCULATE (
        MAX ( 'Table'[Date updated] ),
        ALLEXCEPT ( 'Table', 'Table'[task-ID], 'Table'[Date] )
    ) = 'Table'[Date updated],
    1,
    0
)

If a task has more than one status records on a date, it returns 1 for the last status row and 0 for earlier status rows. 

Then create measures

Tasks Open = CALCULATE(DISTINCTCOUNT('Table'[task-ID]),FILTER('Table','Table'[Is Last record] = 1 && 'Table'[Status] = "Open"))+0

Tasks Done = CALCULATE(DISTINCTCOUNT('Table'[task-ID]),FILTER('Table','Table'[Is Last record] = 1 && 'Table'[Status] = "Done"))+0

Total Tasks = DISTINCTCOUNT('Table'[task-ID])

Put the new "Date" column in the slicer instead of "Date updated" column. 

I have attached the pbix file. See if it meets your need.

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.