Distinct count of ID based on Latest Status

[Anonymous]

Hello everyone,

I am new to Power BI and need some help. 

I have Order ID, Order status and Time modified(Time when the Order status was changed) as columns.

My data looks like this:

Order ID	Order Status	Time modified
111	0	10/02/2023 15:00:00
111	7	10/02/2023 15:05:00
112	0	10/02/2023 16:00:00
112	4	10/02/2023 16:05:00
112	0	10/02/2023 17:05:00
222	0	10/02/2023 12:00:00
222	7	10/02/2023 12:10:00
333	0	10/02/2023 11:00:00
333	4	10/02/2023 12:00:00
333	7	10/02/2023 12:02:00
333	0	10/02/2023 12:30:00
444	0	10/02/2023 13:30:00
444	4	10/02/2023 14:30:00

I need the distinct count of Order ID for each day based on the latest Order Status(Order Status changes with time and only the latest Order Status should be considered). 

I need a measure Order Count such that the result looks like this:

Date	Order Status	Order Count
10/02/2023	0	2 //Order ID=112 and 333
10/02/2023	7	2 //Order ID=111 and 222
10/02/2023	4	1 //Order ID=444

Any help would be appreciated.

Thank you,

Akshay

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

[ThxAlot · Accepted solution]

Only one calculated column is needed with WINDOW function; but it requires profound comprehension of DAX.

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

[amitchandak]

@Anonymous , Create measures like

M1  =
VAR MaxTime = CALCULATE(
MAX('Table'[Time modified]),
ALLEXCEPT('Table', 'Table'[Order ID], 'Table'[Date Only])
)
RETURN
IF('Table'[Time modified] = MaxTime, TRUE(), FALSE())

Order Count =
SUMX(
VALUES('Table'[Date Only]),
CALCULATE(
DISTINCTCOUNT('Table'[Order ID]),
[M1] = TRUE()
)
)

Latest Date
https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee884e