Forum Discussion

VikcyD's avatar
VikcyD
Frequent Visitor
1 year ago
Solved

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

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 ...
  • wardy912's avatar
    1 year ago

    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!