Latest Status (for Candidate Tracking)

Hi LindaJonesCT,

This scenario is a typical application of a snapshot fact table. That means, you would transform your status event table into a table that contains for all dates that you want to analyze, all candidates and all their states that were valid at that date. You could create these snapshots for every date, once a week, once a month, at every date when a new status was entered, whatever you prefer. Then you could draw a visual by status and just filter by date and show distinct count of candidates as values.

But you can also use your transactional table structure with a measure.

To do so, first, you need to make sure that selecting a date does not also implicitly select the states, otherwise you are not able to see values for states that have not been entered by any candidate at the selected date. You can achieve this by creating a date dimension table, or a status dimension table, or both. I'd start with a date table, because this also allows you to analyze dates at which no new states were assigned. The date table is just a disconnected Table containing all the dates that you want to analyze.

The simplest way to create the date table is to create a calculated table using the formular:

Dates = CALENDARAUTO()

Then change the datatype of the Date column in the new table from date/time to date.

Alternatively, you can also create a custom date table in PowerQuery.

Finally, doublecheck in data model view that there is no relationship between the new date table und your existing table.

Next you need to create a measure:

Count of Candidate with Date Table = 

    // Get selected date
    VAR _Date = MAX ( 'Date'[Date] )

    // Get selected states
    VAR _States = VALUES ( 'Candidate Status Changes'[Status] )

    // Per candidate, get the latest date of new stats change
    VAR _CandidatesAndDates = 
        CALCULATETABLE (
            ADDCOLUMNS (
                SUMMARIZECOLUMNS (
                    'Candidate Status Changes'[Candidate]
                ),
                "@LastDate",
                CALCULATE (
                    MAX ( 'Candidate Status Changes'[Date] )
                )
            ),
            'Candidate Status Changes'[Date] <= _Date,
            ALL ( 'Candidate Status Changes'[Status] )
        )

    // Per candidate, get max. status set at last date
    VAR _CandidatesAndStatus =
        ADDCOLUMNS (
            _CandidatesAndDates,
            "@MaxStatus",
            VAR _Date = [@LastDate]
            RETURN
            CALCULATE (
                MAX ( 'Candidate Status Changes'[Status] ),
                'Candidate Status Changes'[Date] = _Date,
                ALL ( 'Candidate Status Changes'[Status] )
            )
        )

    RETURN

    COUNTROWS ( FILTER ( _CandidatesAndStatus, [@MaxStatus] IN _States ) )

Now you can create a visual using status and this measure and add a date filter (from the date table) to your page and select your date of interest.

In this file you can explore both approaches, the one with the snapshot fact table and the one with the measure for your table as described above.