Compare strings in previous month

[Anonymous]

Hello. I need a DAX Measure to count how many staff employees got promoted into a leadership position. This is what my data looks like. So it would need to return 1 because 101 was staff. 102's promotion doesnt count because he was already in a leadership position.

ID	Date	Managerial Level	Promotion
101	10/20	Staff
102	10/20	Manager
103	10/20	Staff
104	10/20	Director
101	11/20	Manager	Promotion
102	11/20	Executive Manager	Promotion
103	11/20	Staff
104	11/20	Director

Thanks

[Payeras_BI]

Hello @Anonymous,

Try with this measure:

Count of Promotions from Staff =
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            'Table',
            "Previous managerial level",
                VAR __id = 'Table'[ID]
                VAR __date = 'Table'[Date]
                RETURN
                    MAXX (
                        TOPN (
                            1,
                            FILTER (
                                ALL ( 'Table' ),
                                'Table'[ID] = __id
                                    && 'Table'[Date] < __date
                            ),
                            'Table'[Date], DESC
                        ),
                        'Table'[Managerial Level]
                    )
        ),
        [Previous managerial level] = "STAFF"
            && 'Table'[Promotion] = "Promotion"
    )
)

[amitchandak]

@Anonymous , Try a measure like

calculate( count(Table[Promotion]) , filter(Table, Table[Promotion] = "Promotion" && Table[ Managerial Level] in { "Staff" ,"Manager"}))

or

calculate( count(Table[Promotion]) , filter(Table, Table[Promotion] = "Promotion" && Table[ Managerial Level] in { "Staff" }))

[Anonymous]

@MattAllington Hi. I don't have access to the data source. I can't add new columns. I want to try to solve this using DAX to avoid having to request a change in the database.

[MattAllington]

Power BI is easy when you prep your data. I would add a conditional column using Power Query to indicate if the Mgr level is considered to be a staff promotion. Then you can write a simple DAX formula

CALCULATE(COUNTROWS(Table),table[promotion]="Promotion",table[new column]="from staff")