Get count of active statuses from event log only if they are the most recent status per account

[anthonymachado]

I have a table of account status changes and I want to let the user set a date to see how many accounts had status "Active" at that date. I currently have a slicer that removes status updates after a certain date, but I need to write a DAX measure to get a count of the remaining "Active" statuses ONLY IF they are the most recent status for that account ID.

I've tried many different ways to do this and I can't seem to get an accurate number. I can't do it in Power Query because it needs to be evaluated after the user changes the slicer, so it has to be done in a DAX measure.

Here is some sample data from the table:

Account ID	Status	Status Updated
56147	Active	10/7/2024 19:35
56147	Install Scheduled	9/24/2024 19:16
56147	Pending Drop	9/19/2024 20:19
56147	Pending Drop	9/27/2024 20:53
56147	Pending Install	10/3/2024 21:11
56147	Inactive	7/16/2025 17:44
58004	Pre-Drop CS Contact	9/10/2025 5:58
58004	Pending Drop	9/10/2025 20:39
58004	Drop Complete	9/19/2025 10:59
58004	Install Scheduled	9/19/2025 15:54
58004	Active	9/23/2025 20:19

[Kedar_Pande]

Active Accounts at Date =
VAR SelectedDate = MAX('DateTable'[Date])
RETURN
COUNTROWS(
FILTER(
SUMMARIZE(
FILTER(
ALL('StatusTable'),
'StatusTable'[Status Updated] <= SelectedDate
),
'StatusTable'[Account ID],
"LastStatus",
CALCULATE(
LASTNONBLANK('StatusTable'[Status], 1),
'StatusTable'[Status Updated] <= SelectedDate
)
),
[LastStatus] = "Active"
)
)

If this answer helped, please click Kudos or mark as Solution.
-Kedar
LinkedIn: https://www.linkedin.com/in/kedar-pande

[Nasif_Azam]

Hey  @anthonymachado ,

Step-by-Step Implementation:

1) Make sure your data is loaded properly, let’s call the table StatusLog in Power BI.  

2) Ensure your date slicer is set up as Show data "before" or "between". 

3) Create the DAX Measure:

Active Accounts (as of slicer) :=
VAR Accounts = VALUES ( 'StatusLog'[Account ID] )
RETURN
SUMX (
    Accounts,
    VAR Acc = 'StatusLog'[Account ID]
    VAR LastDt =
        CALCULATE (
            MAX ( 'StatusLog'[Status Updated] ),
            'StatusLog'[Account ID] = Acc
        )
    VAR LastStatus =
        CALCULATE (
            MAXX (
                FILTER (
                    'StatusLog',
                    'StatusLog'[Account ID] = Acc
                        && 'StatusLog'[Status Updated] = LastDt
                ),
                'StatusLog'[Status]
            )
        )
    RETURN IF ( LastStatus = "Active", 1, 0 )
)

4) Add a Card visualization and drag this new measure (Active Accounts (as of slicer) into it.

Output:

 

Also attached the pbix file.

Best Regards,
Nasif Azam

[ThxAlot · Accepted solution]

[anthonymachado]

This worked great! Mine had Status Updated underlined in red as well but it still worked

[parry2k]

@anthonymachado could you give an example output based on some date selections?

[anthonymachado]

If we're looking at the sample data, the output would be 1. Since the most recent status for Account ID 58004 is "Active" and the most recent status for Account 56147 is "Inactive" and I want to count the "Active" statuses if they are the most recent status for each account