Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Compare strings in previous month

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 promo...
Payeras_BI's avatar
Payeras_BI
Icon for Solution Sage rankSolution Sage
5 years ago

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"
    )
)