topic Re: New Column with sum based on another filtered table in DAX Commands and Tips

New Column with sum based on another filtered table

Anonymous — Wed, 19 Jan 2022 12:02:08 GMT

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

Re: New Column with sum based on another filtered table

Fowmy — Wed, 19 Jan 2022 12:23:32 GMT

Anonymous

Can you try this way?

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

Re: New Column with sum based on another filtered table

ValtteriN — Wed, 19 Jan 2022 12:39:02 GMT

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!

Re: New Column with sum based on another filtered table

Anonymous — Wed, 19 Jan 2022 13:05:10 GMT

Thanks! It is correclty working

Re: New Column with sum based on another filtered table

Anonymous — Wed, 19 Jan 2022 13:06:23 GMT

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