Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

New Column with sum based on another filtered table

Hello, 

 

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

 

1.JPG

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

2.JPG

 

"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
1 ACCEPTED SOLUTION
Fowmy
Super User
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]
)
Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

4 REPLIES 4
ValtteriN
Super User
Super User

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!






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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
Super User
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]
)
Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Anonymous
Not applicable

Thanks! It is correclty working

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors