topic Re: Get count of active statuses from event log only if they are the most recent status per account in DAX Commands and Tips

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

anthonymachado — Mon, 27 Oct 2025 22:47:14 GMT

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

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

parry2k — Mon, 27 Oct 2025 22:27:37 GMT

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

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

anthonymachado — Mon, 27 Oct 2025 22:46:47 GMT

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

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

ThxAlot — Tue, 28 Oct 2025 00:10:28 GMT

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

Nasif_Azam — Tue, 28 Oct 2025 07:49:16 GMT

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

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

Kedar_Pande — Tue, 28 Oct 2025 12:59:50 GMT

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

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

anthonymachado — Tue, 28 Oct 2025 15:03:17 GMT

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