New Column with sum based on another filtered table

[Anonymous]

Hello, 

Currently I have a table ("Projects") with the following values

I am interesting in knowing the effort per each day of the year, so I have created another table ("Calendar") with the following values

"Date" column is calculated with the following formula 

Calendar = CALENDAR(DATE(2022,01,01),DATE(2022,11,01))

"Number of projects" with the following formula: 

Number of projects = COUNTROWS(
                         FILTER(Projects,Projects[Start Date]<='Calendar'[Date] && Projects[Finish Date]>='Calendar'[Date]))

But I am not able to calculate the "Effort necessary" column. Any tips? I need something similar to COUNTROWS but summing the column "Effort per day" instead of counting. Using CALCULATE as in code bellow is not working (see message below) 

Effort = CALCULATE(
                  SUM(Projects[Effort per day]),Projects[Start Date]<='Calendar'[Date],Projects[Finish Date]>='Calendar'[Date])

"The expression contains columns from multiple tables, but only columns from a single table can be used in a True/False expression that is used as a table filter expression."

Thanks

[ValtteriN]

Hi,

For values which last a certain amount of time you can use the following DAX pattern:

Measure =

var c_date = MAX('Calendar'[Date])

return

CALCULATE(SUM(Table[Value]),

FILTER(Table,Table[StartDate]<=c_date &&

Table[EndDate])>=c_date))

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

[Anonymous]

Hello, 

I think this is a Measure, and my idea was adding a new column. I am not able to put this in a column. 

Thanks anyway

[Fowmy · Accepted solution]

@Anonymous 

Can you try this way?

Effort =
SUMX (
    FILTER (
        Projects,
        Projects[Start Date] <= 'Calendar'[Date]
            && Projects[Finish Date] >= 'Calendar'[Date]
    ),
    Projects[Effort per day]
)

[Anonymous]

Thanks! It is correclty working