Move values from one category of a column to another category (within the same column)

[milyanaa]

I have a query about how I can break down values from one column named System Status, although I'm unsure how to explain what I need.

Each system has multiple components, all with different creation dates. For instance, 'System A' has three components (Comp. 1, Comp. 2, Comp. 3). The Creation Dates for each component may differ.

I created a column named 'System Status' which breaks down each System into 'In Progress' (Creation Date = 2022) or 'Done' (Creation Date = 2023). However, this is done by Component rather than the distinct System. So my totals for each category do not match up.

This is my target: Component A = 'Done' only when all components have a Creation Date of '2023'. Component A = 'In Progress' if at least one component has a Creation Date of '2022'.

This is how my data is broken down (via Matrix):

Rows:

Community

System Status ('Done', 'Pending')

System Name

Values:

Creation date

# of Systems (name is misleading, as this is really by component)

Example of Matrix (current): 

Community | Most Recent Date | # of Systems

Comm.1 | 2023 | 50

+ 'Done' | 2023 | 40

    SYSTEM.1 | 2023 | 1

+ 'In Progress' | 2022 | 30

    SYSTEM.1 | 2022 | 1

Example of Matrix (target): 

Community | Most Recent Date | # of Systems

+ Comm.1 | 2023 | 50

    + 'Done' | 2023 | 10

        SYSTEM.2 | 2023 | 1

    + 'In Progress' | 2022 | 40

        SYSTEM.1 | 2022 | 1

*Key: 

+ = Representation of Drilldown

| = Separation of Columns

[ChiragGarg2512]

A column is created using Earlier() as follows:

New column = 
var _cnt = countrows(Filter(Table, Table[COmponent] = Earlier(Table[COmponent]) && Year(Table[Date]) = Year(Today())))
var _comp_cnt = countrows(Filter(Table, Table[COmponent] = Earlier(Table[COmponent])))
return 
if(_cnt = _comp_cnt , "Done", "Pending")

If problem persists, share a sample data in table format.

For more information refer to this: 

Power BI DAX- Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8

[milyanaa]

So, let's say I start with this table: 

Community	System	Component	Creation Date
PARK	ZEUS	BLOCK1	2023-05-16
PARK	ZEUS	BLOCK2	2022-01-05

In the above example, System 'ZEUS' would be in the 'In Progress' status, as there is one Component which does not have a creation date within the current year. 

If both Components of ZEUS had creation dates of 2023, then System ZEUS would be in the 'Complete' status. 

This is why I created a calculated column for 'System Status,' which looks like this: 

Community	Creation Date (Most Recent)	# of Systems
+PARK	2023-05-16	10
+In Progress	2022-01-05	5
ZEUS	2022-01-05	1
+Completed	2023-05-16	8
ZEUS	2023-05-16	1

This is the target that I want:

Community	Creation Date (Most Recent)	# of Systems
+PARK	2023-10-09	10
+In Progress	2022-01-05	8
ZEUS	2022-01-05	1
+Completed	2023-10-09	2
HERA	2023-10-09	1

(I also want to mention that anything occurring a year before the previous one (2021 or before) would be considered as 'Expired.')