Forum Discussion
Distinct count of ID based on Latest Status
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
Hi,
PBI file attached.
Only one calculated column is needed with WINDOW function; but it requires profound comprehension of DAX.
3 Replies
- amitchandak
Super User
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 - Ashish_Mathur
Hi,
PBI file attached.
- ThxAlot
Only one calculated column is needed with WINDOW function; but it requires profound comprehension of DAX.
