Forum Discussion

hungbm1's avatar
hungbm1
New Member
3 years ago
Solved

SUMX problem

I had a problem while using SUMX. I need to count the number of project that start in November, but the total is wrong (as the image bellow). I'm so confuse =((    
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi hungbm1 

    You're missing context transition. Please try

    Number of new project =
SUMX (
    DISTINCT ( D_Project[Project Code] ),
    CALCULATE (
        VAR Start_of_period =
            MIN ( 'Calendar'[Date] )
        VAR End_of_period =
            MAX ( 'Calendar'[Date] )
        VAR Start_date_of_project =
            SELECTEDVALUE ( D_Project[Plan Start date (65)] )
        RETURN
            IF (
                Start_of_period <= Start_date_of_project
                    && Start_date_of_project <= End_of_period,
                1,
                0
            )
    )
)
tamerj1's avatar
tamerj1
Community Champion
3 years ago

Hi hungbm1 

You're missing context transition. Please try

Number of new project =
SUMX (
    DISTINCT ( D_Project[Project Code] ),
    CALCULATE (
        VAR Start_of_period =
            MIN ( 'Calendar'[Date] )
        VAR End_of_period =
            MAX ( 'Calendar'[Date] )
        VAR Start_date_of_project =
            SELECTEDVALUE ( D_Project[Plan Start date (65)] )
        RETURN
            IF (
                Start_of_period <= Start_date_of_project
                    && Start_date_of_project <= End_of_period,
                1,
                0
            )
    )
)