Counting orders based on their latest status

[Maciej1992]

Hello,

For some time now I am strugling with measure that will count orders based on their last status.

I tackled this from multiple angles and even ask ChatGPT for help, but couldn't find a solution.

My goal is table visualization with columns for:

names of statuses in first column (STATUS_DICTIONARY[NAME]),

number of orders which have given status as their latest,

% of GT

My model looks like this:

ORDERS[ID] - 1 to * - ORDERS_STATUS[ID_ORDER]

STATUS_DICTIONARY[ID] - 1 to * - ORDERS_STATUS[ID_STATUS]

And below is some sample data:

ORDERS

ID	NUMBER
1	23/01/1
2	23/01/2
3	23/01/3
4	23/01/4
5	23/01/5

ORDERS_STATUS

ID	ID_ORDER	ID_STATUS	DATE_CHANGE
1	1	1	26.09.2023
2	1	2	26.09.2023
3	2	1	26.09.2023
4	3	1	27.09.2023
5	4	1	27.09.2023
6	1	3	27.09.2023
7	2	5	28.09.2023
8	3	2	28.09.2023
9	3	3	29.09.2023
10	5	1	30.09.2023
11	4	2	30.09.2023

STATUS_DICTIONARY

ID	NAME
1	aaa
2	bbb
3	ccc
4	ddd
5	eee

Based on all above I expect result to look like this:

STATUS NAME	NUMBER OF ORDERS	% OF GT
aaa	1	20%
bbb	1	20%
ccc	2	40%
ddd	0	0%
eee	1	20%

I will be very gratefull for any help.

Thank you!

[ERD · Accepted solution]

@Maciej1992 , here are the measures:

orders_amt = 
VAR t =
    FILTER (
        ORDERS_STATUS,
        VAR order_id = CALCULATE ( SELECTEDVALUE ( ORDERS[ID] ) )
        VAR maxDt =
            CALCULATE (
                MAX ( ORDERS_STATUS[DATE_CHANGE] ),
                ORDERS_STATUS[ID_ORDER] = order_id,
                ALL ( ORDERS_STATUS )
            )
        VAR maxStatus =
            CALCULATE (
                MAX ( ORDERS_STATUS[ID_STATUS] ),
                ORDERS_STATUS[DATE_CHANGE] = maxDt,
                ORDERS_STATUS[ID_ORDER] = order_id
            )
        RETURN
            ORDERS_STATUS[ID_ORDER] = order_id && ORDERS_STATUS[ID_STATUS] = maxStatus
    )
RETURN
    COUNTROWS ( t )

% OF GT = 
VAR latestRows = [orders_amt]
VAR allrows =
    CALCULATE (
        SUMX ( VALUES ( STATUS_DICTIONARY[NAME] ), [orders_amt] ),
        ALL ( STATUS_DICTIONARY[NAME] )
    )
RETURN
    latestRows / allrows

[Maciej1992]

Worked like a charm! Thank you very much. You truly are a Rockstar 🙂