Hello All,
I have a pretty easy formula that I need to tweak. Below is my table data, I'll I'm trying to do is write a calculated column that sums any the values in (Value to Sum) on each row less than or equal to the "Date" and "Investor".
I've tried using the calculate function but nothing's filtering correctly I keep getting the total of 327. Here's the formula I've written
=CALCULATE(SUM([Value to Sum]),filter(Table1,Table1[Date]<=[Date]),FILTER(Table1,Table1[Investor]=[Investor]))
The Excel formula would look like this...sumifs(value to sum, date,"<=&Date", investor, "@investor")
So for example the "red" investor with the date of 6/15/2016" should return a value of 79 (57+22). Any help would be greatly appreciated, thanks!
<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
I have a pretty easy formula that I need to tweak. Below is my table data, I'll I'm trying to do is write a calculated column that sums any the values in (Value to Sum) on each row less than or equal to the "Date" and "Investor".
I've tried using the calculate function but nothing's filtering correctly I keep getting the total of 327. Here's the formula I've written
=CALCULATE(SUM([Value to Sum]),filter(Table1,Table1[Date]<=[Date]),FILTER(Table1,Table1[Investor]=[Investor]))
The Excel formula would look like this...sumifs(value to sum, date,"<=&Date", investor, "@investor")
So for example the "red" investor with the date of 6/15/2016" should return a value of 79 (57+22). Any help would be greatly appreciated, thanks!
| Deal | Investor | Date | Value to Sum |
| 1 | red | 1/1/2016 | 57 |
| 2 | green | 5/5/2016 | 97 |
| 3 | blue | 10/2/2014 | 52 |
| 3 | yellow | 11/12/2015 | |
| 5 | hello | 1/5/2013 | 49 |
| 4 | red | 6/15/2016 | 22 |
| 7 | yellow | 10/14/2015 | 50 |
<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
