Measure to count applicant ids where stage id = a certain value per year

[KyleFerrero]

I need to write a measure that counts the unique applicant IDs in my snapshots table whose stage ID is a certain value and I need this measure to be accurate year by year as I will be putting it in a table with years on the rows. I already have measure that count new applicants, cumulative applicants, and cumulative applicants who make it to the stage, but I cannot get the measure for new applicants who make it to the stage to work. Common problems that I am having are that the measure will count all of the unique applicant IDs whose stage ID matches for the year, but not filter out those who entered the stage in the prior year. Below are some of my measures and the table that I am trying to create without the new applicants who made it to stage X measure:

New Applicants =

CALCULATE(

DISTINCTCOUNT(SnapshotApplicants[ApplicantsID]),

FILTER(

    SnapshotApplicants,

  YEAR([Min Snapshot Date]) = SELECTEDVALUE('Calendar'[Year])

)

)

Cumulative Applicants =

DISTINCTCOUNT(SnapshotApplicants[ApplicantsID])

Cumulative Applicants who made it to Stage X=

VAR SelectedYear = SELECTEDVALUE(Calendar[Year])

VAR MaxDateInSelectedYear = MAXX(FILTER(ALL('Calendar'), Calendar[Year] = SelectedYear), Calendar[Date])

VAR MinSnapshotDate = MINX(ALL(SnapshotApplicants), SnapshotApplicants[SnapshotDate])

RETURN

CALCULATE(

    DISTINCTCOUNT(SnapshotApplicants[ApplicantsID]),

    SnapshotApplicants[stage.id] IN {"1487532452977"},

    FILTER(

        ALL(SnapshotApplicants),

        SnapshotApplicants[SnapshotDate] >= MinSnapshotDate &&

        SnapshotApplicants[SnapshotDate] <= MaxDateInSelectedYear &&

        NOT(ISBLANK(RELATED(Applicants[Name])))

    )

)

[v-tangjie-msft · Accepted solution]

Hi @KyleFerrero ,

You can try this measure, which agrees with this calculated column output.

Measure = 
VAR EntryDate = 
CALCULATE(
    MIN(SnapshotApplicants[SnapshotDate]),FILTER(ALLSELECTED('SnapshotApplicants'),
        SnapshotApplicants[stage.id] IN {"1487532452977"}))
        RETURN
        IF(NOT(ISBLANK(EntryDate)), EntryDate, BLANK())

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[KyleFerrero · Accepted solution]

After adjusting my approach, I was able to get the desired results with a measure-based solution. The solution is broken down into two measures:

Stage X Applicants Per Year =

IF (

    ISBLANK ( SELECTEDVALUE('Calendar'[Year]) ),

    BLANK(),

    CALCULATE(

        COUNTROWS(Applicants),

        FILTER(

            Applicants,

            [Made it to Stage Check Per Year] = "Yes"

        )

    )

)

Made it to Stage Check Per Year =

Var ApplicantID = SELECTEDVALUE(Applicants[ApplicantID])

VAR SpecificStageIDs = {"1487532452977", "1711544054156", "1711049256981", "1707413308135", "1487532504471", "1710875527443", "1707156306916", "1487532622128", "1487532692566", "1567607505153", "1618340610468", "1707231361818", "1644620123322"}  

VAR ApplicantHasStages =

    CALCULATE(

        COUNTROWS(SnapshotApplicants),

        SnapshotApplicants[ApplicantsID] = ApplicantID,

        SnapshotApplicants[stage.id] IN SpecificStageIDs

    )

Var MadeItToStageCheck = IF(ApplicantHasStages > 0, "Yes", "No")

Var FirstDateInStage =

CALCULATE(

    MIN(SnapshotApplicants[SnapshotDate]),

    FILTER(

        ALL(SnapshotApplicants),

    SnapshotApplicants[ApplicantsID] = ApplicantID &&

    MadeItToStageCheck = "Yes" &&

    SnapshotApplicants[stage.id] IN SpecificStageIDs

)

)

VAR SelectedYear = SELECTEDVALUE('Calendar'[Year])

VAR PerYearCheck = IF(SelectedYear = YEAR(FirstDateInStage), "Yes", "No")

RETURN

PerYearCheck

[v-tangjie-msft · Accepted solution]

Hi @KyleFerrero ,

You can try this measure, which agrees with this calculated column output.

Measure = 
VAR EntryDate = 
CALCULATE(
    MIN(SnapshotApplicants[SnapshotDate]),FILTER(ALLSELECTED('SnapshotApplicants'),
        SnapshotApplicants[stage.id] IN {"1487532452977"}))
        RETURN
        IF(NOT(ISBLANK(EntryDate)), EntryDate, BLANK())

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[KyleFerrero]

Update: I was able to get the desired results through the use of a calculated column, but I am wondering about a measure only based approach for learning purposes if nothing else. How would I get these results without the use of a calculated column.

Calculated column on Applicants table:

Stage X Entry Date = 
VAR EntryDate = 
CALCULATE(
    MIN(SnapshotApplicants[SnapshotDate]),
        SnapshotApplicants[stage.id] IN {"1487532452977"})
        RETURN
        IF(NOT(ISBLANK(EntryDate)), EntryDate, BLANK())

Measure:

New Applicants who made it to Stage X= 
VAR NewInterviewCount =
CALCULATE(
    DISTINCTCOUNT(Applicants[ApplicantID]),
    YEAR(Applicants[Testing Entry Date]) = SELECTEDVALUE('Calendar'[Year])
    )
    RETURN
    IF(NOT(ISBLANK(SELECTEDVALUE('Calendar'[Year]))), NewInterviewCount, BLANK())

[v-tangjie-msft]

Hi @KyleFerrero ,

Sorry for that the information you have provided is not making the problem clear to me.

Please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Neeko Tang

[KyleFerrero]

@v-tangjie-msft I will try to explain the model/problem the best I can. I have a dataset that is tracking the application pipeline for my company. In this dataset, I am primarily focusing on the snapshots table which contains Applicant IDs, Stage IDs, and Snapshot dates. These have many to one relationship with the calendar, applicants, and stages tables, and a new snapshot of the pipeline is taken each day. I am trying to create a matrix visual that shows years from the calendar table on the rows, and a few measures as the values. I would like these measures to be cumulative applicants, cumulative applicants who made it to Stage X, new applicants, and new applicants who made it to stage X. All of the measures are working properly outside of the new applicants who made it to Stage X measure. 

New Applicants =

CALCULATE(

DISTINCTCOUNT(SnapshotApplicants[ApplicantsID]),

FILTER(

    SnapshotApplicants,

  YEAR([Min Snapshot Date]) = SELECTEDVALUE('Calendar'[Year])

)

)

Min Snapshot Date =

CALCULATE(

    MIN(SnapshotApplicants[SnapshotDate]),

    ALLEXCEPT(SnapshotApplicants, SnapshotApplicants[ApplicantsID])

)

Cumulative Applicants =

DISTINCTCOUNT(SnapshotApplicants[ApplicantsID])

Cumulative Applicants =

DISTINCTCOUNT(SnapshotApplicants[ApplicantsID])

Cumulative Applicants who made it to Stage X=

VAR SelectedYear = SELECTEDVALUE(Calendar[Year])

VAR MaxDateInSelectedYear = MAXX(FILTER(ALL('Calendar'), Calendar[Year] = SelectedYear), Calendar[Date])

VAR MinSnapshotDate = MINX(ALL(SnapshotApplicants), SnapshotApplicants[SnapshotDate])

RETURN

CALCULATE(

    DISTINCTCOUNT(SnapshotApplicants[ApplicantsID]),

    SnapshotApplicants[stage.id] IN {"1487532452977"},

    FILTER(

        ALL(SnapshotApplicants),

        SnapshotApplicants[SnapshotDate] >= MinSnapshotDate &&

        SnapshotApplicants[SnapshotDate] <= MaxDateInSelectedYear &&

        NOT(ISBLANK(RELATED(Applicants[Name])))

    )

)

These measures work correctly and generate the results below, but I am having trouble with the new applicants who made it to Stage X measure:

The Cumulative measures take the new applicants for that year, and add this to the amount of new applicants in all prior years (There are 14,434 total applicants in my table and 4,614 made it to stage X.) The new applicants who made it to stage X measure is off. It starts out correctly counting the 1,034 applicants who made it to stage X in 2022, but since the cumulative total in 2023 is 2,722, the new applicants who made it to stage X for 2023 should be 1,688 (2,722-1,034). I need this measure to count the unique applicant IDs in the snapshots table, and filter it so that it only counts those who earliest date with the stage ID "1487532452977" falls in the selected calendar year on the row of the table. Common problems are that my measure counts applicant IDs who entered the stage in 2022, but are still in the stage in 2023, (example: the applicant ID was in the stage for 12/25/2022 through 1/5/2023) or not counting applicants who were in a different stage prior to 2023, but entered stage X in 2023. Let me know if you have any more specific questions.

[ahadkarimi]

Hi @KyleFerrero, give this a try, and if you encounter any issues, let me know.

New Applicants who made it to Stage X = 
CALCULATE(
    DISTINCTCOUNT(SnapshotApplicants[ApplicantsID]),
    FILTER(
        SnapshotApplicants,
        SnapshotApplicants[stage.id] = "1487532452977" && -- Condition for stage X
        YEAR(SnapshotApplicants[SnapshotDate]) = SELECTEDVALUE('Calendar'[Year])
    ),
    FILTER(
        SnapshotApplicants, 
        YEAR(SnapshotApplicants[Min Snapshot Date]) < SELECTEDVALUE('Calendar'[Year])
    )
)

Did I answer your question? If so, please mark my post as the solution! ✔️
Your Kudos are much appreciated! Proud to be a Solution Specialist!

[KyleFerrero]

@ahadkarimi 

Did not work, this is what my Min Snapshot Date measure looks like as I forgot to include it and you used it in your solution:

Min Snapshot Date =

CALCULATE(

    MIN(SnapshotApplicants[SnapshotDate]),

    ALLEXCEPT(SnapshotApplicants, SnapshotApplicants[ApplicantsID])

)

Here's the results of your solution "New Applicants who made it to Stage X Solution" measure

[KyleFerrero]

This is my current measure:

New Applicants who made it to Stage X=

CALCULATE(

    DISTINCTCOUNT(SnapshotApplicants[ApplicantsID]),

    FILTER(

        SnapshotApplicants,

        SnapshotApplicants[stage.id] = "1487532452977" &&

        YEAR([Min Snapshot Date]) = SELECTEDVALUE('Calendar'[Year])

    )

)

and here is what the table looks like:

As you can see the numbers are slightly off and I believe this is because my measure is not counting those who entered the pipeline in a prior year but entered stage X in the selected year.