Forum Discussion
Countrows last date
I have this table
| id | state | date |
| 1 | active | 01/01/2020 |
| 1 | reserved | 05/01/2020 |
| 1 | suspended | 08/01/2020 |
| 1 | sold | 15/01/2020 |
| 2 | active | 02/01/2020 |
| 2 | suspended | 03/01/2020 |
| 2 | active | 05/01/2020 |
| 3 | active | 05/01/2020 |
| 4 | active | 06/01/2020 |
| 4 | sold | 08/01/2020 |
I want count rows of distinct IDs where last date equal to "active"
Result expected is: 2 (last date from these 2 IDs have the state "active".
Can you help me?
Thanks
Hi youconnect ,
I am understanding your logic and have made the following calculation:
count_active_id = var last_active_date = CALCULATE(MAX(Sheet2[date]),FILTER(ALLEXCEPT(Sheet2,Sheet2[id],Sheet2[date]),Sheet2[state]="active")) var last_date = CALCULATE(MAX(Sheet2[date]),ALLEXCEPT(Sheet2,Sheet2[id])) return CALCULATE(COUNTROWS(FILTER(Sheet2,last_active_date=last_date)))Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
This will do what you describe, but it is 1, not 2.
Latest Record Count = VAR LatestDate = CALCULATE( LASTDATE('Sample Data'[Date]), 'Sample Data'[state] = "active" ) VAR ActiveIDCount = CALCULATE( DISTINCTCOUNT('Sample Data'[id]), FILTER( 'Sample Data', 'Sample Data'[Date] = LatestDate ) ) RETURN COALESCE(ActiveIDCount,0)the last date of an active item is Jan 6, 2020, and only one ID has that date.
How are you getting 2?No.
last date for id 1 is 15/01/2020 and state is not active, not countlast date for id 2 is 05/01/2020 and state is active, count
last date for id 3 is 05/01/2020 and state is active, count
last date for id 4 is 08/01/2020 and state is not active, not count.
So count is 2
I suspect there is an cleaner way to do this, but this does the trick.
Counting Measure = VAR FirstTable = ADDCOLUMNS ( SUMMARIZECOLUMNS ( 'Sample Data'[id] ), "Date2", LASTDATE ( 'Sample Data'[Date] ) ) VAR CombinedTable = NATURALINNERJOIN ( 'Sample Data', firsttable ) VAR RowCount = COUNTROWS ( FILTER ( FILTER ( CombinedTable, [Date] = [Date2] ), [state] = "active" ) ) RETURN COALESCE( RowCount, 0)
