Help Needed: DAX Logic to Count Sales Reps Meeting 2 Monthly Achievement Criteria

[VikcyD]

I have sales data and I'm trying to identify how many unique Sales Representatives qualify as 'winners'. A Sales Rep should be counted as a winner only if their achievement in the previous month was greater than 99.5% and their achievement in the current month is at least 94.5%. I've tried multiple approaches in DAX, but the result always returns 0. I'd appreciate help in figuring out what might be going wrong.

Follwoing is my Dax Code:

Sales Ach_Status_Count =
SUMX(
VALUES (SaleData[Terr_Code] ),
VAR Terr = SaleData[Terr_Code]

VAR CurrAch =
CALCULATE
(
DIVIDE([TotalSaleCYValue_INR], [Target_ValueCY_INR], 0),
SaleData[Terr_Code] = Terr,
'Calendar'[Date] = EOMONTH(MAX(SaleData[Date]), 0)
)

VAR PrevAch =
CALCULATE
(
DIVIDE([TotalSaleCYValue_INR], [Target_ValueCY_INR], 0),
SaleData[Terr_Code] = Terr,
'Calendar'[Date] = EOMONTH(MAX(SaleData[Date]), -1)
)

RETURN
IF (
CurrAch >= 0.945 && PrevAch >= 0.995,
1,
0
)
)

[wardy912 · Accepted solution]

Hi @VikcyD 

Please try this

Sales Ach_Status_Count =
CALCULATE (
    COUNTROWS (
        FILTER (
            VALUES ( SaleData[Terr_Code] ),
            VAR Terr = SaleData[Terr_Code]

            VAR CurrAch =
                CALCULATE (
                    DIVIDE ( [TotalSaleCYValue_INR], [Target_ValueCY_INR], 0 ),
                    FILTER (
                        ALL ( 'Calendar' ),
                        'Calendar'[Month] = MONTH ( MAX ( SaleData[Date] ) )
                            && 'Calendar'[Year] = YEAR ( MAX ( SaleData[Date] ) )
                    ),
                    SaleData[Terr_Code] = Terr
                )

            VAR PrevAch =
                CALCULATE (
                    DIVIDE ( [TotalSaleCYValue_INR], [Target_ValueCY_INR], 0 ),
                    FILTER (
                        ALL ( 'Calendar' ),
                        'Calendar'[Month] = MONTH ( EOMONTH ( MAX ( SaleData[Date] ), -1 ) )
                            && 'Calendar'[Year] = YEAR ( EOMONTH ( MAX ( SaleData[Date] ), -1 ) )
                    ),
                    SaleData[Terr_Code] = Terr
                )

            RETURN
                CurrAch >= 0.945 && PrevAch >= 0.995
        )
    )
)

I hope this helps, please give a thumbs up and mark as solved if it does, thanks!

[VikcyD]

You are amzing Thanks @wardy912 

[VikcyD]

Example Caclucted in excel