Last Status Summary

[Anonymous]

Hello guys, can you help me on my issue here?

So basically I want to create a summary table based on the daily status of an ID.

I have a table as shown below:

ID	Date	Project	Daily Status
ID1	15/10/2020	Project A	Registered
ID1	16/10/2020	Project A	Follow Up
ID1	17/10/2020	Project A	Follow Up
ID1	18/10/2020	Project A	Follow Up
ID1	01/11/2020	Project B	Registered
ID1	02/11/2020	Project B	Follow Up
ID1	03/11/2020	Project B	Follow Up
ID1	04/11/2020	Project B	Finished
ID2	15/10/2020	Project Z	Registered
ID2	16/10/2020	Project Z	Follow Up
ID2	17/10/2020	Project Z	Follow Up
ID2	18/10/2020	Project Z	Finished
ID2	01/11/2020	Project Y	Registered
ID2	02/11/2020	Project Y	Follow Up
ID2	03/11/2020	Project Y	Follow Up
ID2	04/11/2020	Project Y	Finished

And my goal is to create something like this:

ID	Status
ID1	Active
ID2	Finished

the logic is ID1 still active because he is still on "Follow Up" status on project A even though he is "Finished" on project B, vice versa for ID2.


Any suggestion?

Thank you

[amitchandak · Accepted solution]

@Anonymous , Try a measure like

measure =
var _1 = calculate(lastnonblankvalue(Table[Date],max(Table[Status])), filter(allselected(table), Table[ID] = max(Table[ID])))
return
if (_1 = "Finished", "Finished" , "Active")

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

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 Measure = 
var numproject = 
CALCULATE(
    DISTINCTCOUNT('Table'[Project]),
    ALLEXCEPT('Table','Table'[ID])
)
var numstatus = 
CALCULATE(
    COUNTROWS('Table'),
    FILTER(
        ALLEXCEPT('Table','Table'[ID]),
        [Daily Status]="Finished"
    )
)
return
IF(
    numstatus<numproject,
    "Active",
    "Finished"
)

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.

[Anonymous]

Hey @v-alq-msft thank you for your effort to not only give the solution, also attach the pbix file. once again, thank you

[amitchandak · Accepted solution]

@Anonymous , Try a measure like

measure =
var _1 = calculate(lastnonblankvalue(Table[Date],max(Table[Status])), filter(allselected(table), Table[ID] = max(Table[ID])))
return
if (_1 = "Finished", "Finished" , "Active")

[Anonymous]

thank you for your solution man, it really helps me #kudos