Get the latest status for an ID at the end of each period

[jtooke]

Hi,

I am looking to create a new table based on a data table to summarize the data based on the status at the end of each period. The data will need to be summarized at several levels. 

My data looks like this. Quarter Period refers to the quarter of the ID Created field. I would want to evaluate each ID to determine the Latest Status at the end of each Quarter Period. For example, ID "D" would be counted as Open in the Current Quarter -3 group, and Closed in the Current Quarter -2 group. ID "A" would only be counted as Open in the Current Quarter group (all other statuses within the same quarter would be irrelevant).

ID	Status	ID Created	ID Updated	Group	Country	Quarter Period
A	Open	1/1/2024	1/1/2024	ABC	US	Current Quarter
B	Open	2/1/2024	2/1/2024	ABC	US	Current Quarter
A	Open	1/1/2024	1/8/2024	ABC	US	Current Quarter
A	Closed	1/1/2024	########	ABC	US	Current Quarter
C	Closed	1/1/2023	4/1/2023	DEF	England	Current Quarter -4
D	Open	6/1/2023	6/1/2023	DEF	England	Current Quarter -3
E	Open	10/1/2023	########	DEF	US	Current Quarter -1
B	Closed	2/1/2024	########	ABC	US	Current Quarter
D	Closed	6/1/2023	8/1/2023	DEF	England	Current Quarter -3
F	Open	8/1/2023	8/1/2023	ABC	England	Current Quarter -2

Thank you!

[danextian · Accepted solution]

Hi @jtooke 

For example, ID "D" would be counted as Open in the Current Quarter -3 group, and Closed in the Current Quarter -2 group. ID  Using the data provicded, there is only one quarter for D and assuming that the basis for the latest status is updated date, D would be tagged as Closed for both rows. Can you please clarify this?

Max Status Per Quarter = 
VAR _MAX_DATE =
    CALCULATE (
        MAX ( 'Table'[ID Updated] ),
        ALLEXCEPT ( 'Table', 'Table'[ID], 'Table'[Quarter Period] )
    )
VAR __RESULT =
    CALCULATE (
        MAX ( 'Table'[Status] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[ID], 'Table'[Quarter Period] ),
            'Table'[ID Updated] = _MAX_DATE
        )
    )
RETURN
    __RESULT

Max Status Per Quarter2 = 
IF (
    'Table'[Status] = 'Table'[Max Status Per Quarter],
    'Table'[Max Status Per Quarter]
)

[jtooke]

Hi @danextian ,

Thank you for your help! The reason that D would be Open in the Current Quarter -3 group was that as of June 1, 2023, D has open status, and is not updated to closed until Aug 1, 2023. Current Quarter -3 would end on June 30, 2023, so at that time D was still open.

Given this problem, I have decided to push the math to a different solution rather than use Power BI, but thank you again for your reply!