Employee month level movement status for Dropped off, New added, and No change.

[Jessie666]

Hi everyone,

I'm working on a DAX to find out the employee movement status for "Dropped Off", "New this month", and "No change" for two pay months. My Dax somehow return everyone in paymonth 3 as Dropped off and everyone in paymonth 4 as New this month.  Can someone help me to fix my DAX? The goal is to add a new column to show the status changes so I can use it as a filter (measurement can't be filter). I also tried to do it in the query editor but it was harder than DAX. 

Current code I have: 

Emp Status =

VAR TotalPersonsWithPaymonth3 =

    CALCULATE (

        DISTINCTCOUNT ( Sheet1[PERSON_ID]),

        Sheet1[PayMonth] = "3"

    )

VAR TotalPersonsWithPaymonth4 =

    CALCULATE (

        DISTINCTCOUNT ( Sheet1[PERSON_ID] ),

        Sheet1[PayMonth] = "4"

    )

RETURN

    IF (

        TotalPersonsWithPaymonth3 > 0 && TotalPersonsWithPaymonth4 = 0,

        "Dropped Off",

        IF (

            TotalPersonsWithPaymonth3 = 0 && TotalPersonsWithPaymonth4 > 0,

            "New This Month",

           IF (

                TotalPersonsWithPaymonth3 > 0 && TotalPersonsWithPaymonth4 > 0,

                "No Change",

                BLANK ()

        )

    )

)

I appreciate your help!!

[Sahir_Maharaj]

Hello @Jessie666,

Can you please try:

Emp Status =
VAR TotalPersonsWithPaymonth3 =
    CALCULATE (
        DISTINCTCOUNT ( Sheet1[PERSON_ID] ),
        Sheet1[PayMonth] = "3"
    )
VAR TotalPersonsWithPaymonth4 =
    CALCULATE (
        DISTINCTCOUNT ( Sheet1[PERSON_ID] ),
        Sheet1[PayMonth] = "4"
    )
RETURN
    IF (
        TotalPersonsWithPaymonth3 > 0 && TotalPersonsWithPaymonth4 = 0,
        "Dropped Off",
        IF (
            TotalPersonsWithPaymonth3 = 0 && TotalPersonsWithPaymonth4 > 0,
            "New This Month",
            IF (
                TotalPersonsWithPaymonth3 > 0 && TotalPersonsWithPaymonth4 > 0,
                "No Change",
                BLANK ()
            )
        )
    )

Let me know if you might require any further assistance.

[Jessie666]

Hi  Sahir,

Thank you very much for looking into my issue. The code you sent works the same like the one I had in my post. It can't determine who stayed "No Change".  I think this is because for DAX expression only look at the person_id in the row, not everyone in the whole table. If I put this as a measure and in a visual, it will look all of the selected Person_ID in the visual. So, in order to add a new column for status it will need to do in the power query editor. But I haven't figured out how to do so. Please let me know if you know how. I appreciate your help!

Best regards,

Yiyi