New Column with sum based on another filtered table

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

Fowmy

4 years ago

Anonymous

Can you try this way?

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

4 Replies

Fowmy

Super User

4 years ago

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
Not applicable
4 years ago
Thanks! It is correclty working

ValtteriN
Community Champion
4 years ago
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
  Not applicable
  4 years ago
  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

Forum Discussion

New Column with sum based on another filtered table