SUMX is not showing calculating any totals

[Swiya]

I have the following measure:

1. Project Load =

var result = CALCULATE(SUMX(SUMMARIZE(Project,Project[Project Number]),SUM(Project[Project Value])),ALL('Month Table with weeks'))

return result

2. monthly_project_value =

CALCULATE(

    sumx(

        FILTER('Month Table with weeks',

                DATE(YEAR(CALCULATE(MIN(Project[Project Start]), ALL('Month Table with weeks'))), MONTH(CALCULATE(MIN(Project[Project Start]), ALL('Month Table with weeks'))), 1) <= MIN('Month Table with weeks'[First day of Month])

                &&

                DATE(YEAR(CALCULATE(MIN(Project[Project End]), ALL('Month Table with weeks'))), MONTH(CALCULATE(MIN(Project[Project End]), ALL('Month Table with weeks'))), 1) >= MAX('Month Table with weeks'[First day of Month]

                )

            ),

[Project Load]

    )

)

The filter is used to get the project value for a selected month if the project is live during that month else to not show anything. Although the individual rows are coming correctly but the total is not getting calculated and shows empty.

[Deku · Accepted solution]

Wrapped your measure in a SUMX, so we iterate each of the projects like you have in the table, and the total sums all those values

SUMX(
   VALUES( Table[project number],  
CALCULATE(
    sumx(
        FILTER('Month Table with weeks',
                DATE(YEAR(CALCULATE(MIN(Project[Project Start]), ALL('Month Table with weeks'))), MONTH(CALCULATE(MIN(Project[Project Start]), ALL('Month Table with weeks'))), 1) <= MIN('Month Table with weeks'[First day of Month])
                &&
                DATE(YEAR(CALCULATE(MIN(Project[Project End]), ALL('Month Table with weeks'))), MONTH(CALCULATE(MIN(Project[Project End]), ALL('Month Table with weeks'))), 1) >= MAX('Month Table with weeks'[First day of Month]
                )
            ),
[Project Load]
    )
)
)

[Swiya]

This worked perfectly! I just had to add a filter in the value clause

here is the complete dax query:

SUMX(

   CALCULATETABLE(VALUES( project[project number]),all('Month Table with weeks')),  

CALCULATE(

    sumx(

        FILTER('Month Table with weeks',

                DATE(YEAR(CALCULATE(MIN(Project[Project Start]), ALL('Month Table with weeks'))), MONTH(CALCULATE(MIN(Project[Project Start]), ALL('Month Table with weeks'))), 1) <= MIN('Month Table with weeks'[First day of Month])

                &&

                DATE(YEAR(CALCULATE(MIN(Project[Project End]), ALL('Month Table with weeks'))), MONTH(CALCULATE(MIN(Project[Project End]), ALL('Month Table with weeks'))), 1) >= MAX('Month Table with weeks'[First day of Month]

                )

            ),

[Project Load]

    )

)

)

[manikumar34]

@Swiya 
monthly_project_value =
SUMX (
VALUES ( 'Month Table with weeks'[First day of Month] ), -- Ensures row-wise calculation
VAR MinStartDate = CALCULATE ( MIN ( Project[Project Start] ), ALL ( 'Month Table with weeks' ) )
VAR MaxEndDate = CALCULATE ( MIN ( Project[Project End] ), ALL ( 'Month Table with weeks' ) )
RETURN
IF (
DATE ( YEAR ( MinStartDate ), MONTH ( MinStartDate ), 1 ) <= MIN ( 'Month Table with weeks'[First day of Month] )
&&
DATE ( YEAR ( MaxEndDate ), MONTH ( MaxEndDate ), 1 ) >= MAX ( 'Month Table with weeks'[First day of Month] ),
[Project Load], -- Use your existing measure for the project value
BLANK () -- Hide values if the project is not live in that month
)
)

[Swiya]

This is also not working