Forum Discussion
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
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
Anonymous
Can you try this way?Effort = SUMX ( FILTER ( Projects, Projects[Start Date] <= 'Calendar'[Date] && Projects[Finish Date] >= 'Calendar'[Date] ), Projects[Effort per day] ) - ValtteriN
Community Champion
Hi,
For values which last a certain amount of time you can use the following DAX pattern:Measure =var c_date = MAX('Calendar'[Date])returnCALCULATE(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!
