Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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") wit...
  • Fowmy's avatar
    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]
)
ValtteriN's avatar
ValtteriN
Icon for Community Champion rankCommunity 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's avatar
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