cancel
Showing results for
Did you mean:
Member

## sum amounts if end date is in next year

Hi PBI Experts,

I need your help with creation fo measure to calculate amount if end date is in "next year". I have start date and end date (which is actually is start date + 12 moths), name, amount and one more date. E.g. If end date is for exammple in Ferbuary 2017, then for January and February add these amounts to total by month.

Sampe table with data and expected results below:

 name strat date end date amount amount_date what we should sum in 2017 Chris 6/1/2016 5/31/2017 1000 6/1/2016 Chris 6/1/2016 5/31/2017 1000 7/1/2016 Chris 6/1/2016 5/31/2017 1000 8/1/2016 Chris 6/1/2016 5/31/2017 1000 9/1/2016 Chris 6/1/2016 5/31/2017 1500 10/1/2016 Chris 6/1/2016 5/31/2017 1500 11/1/2016 Chris 6/1/2016 5/31/2017 1500 12/1/2016 Chris 6/1/2016 5/31/2017 1500 1/1/2017 1500 Chris 6/1/2016 5/31/2017 1500 2/1/2017 1500 Chris 6/1/2016 5/31/2017 1500 3/1/2017 1500 Chris 6/1/2016 5/31/2017 1500 4/1/2017 1500 Chris 6/1/2016 5/31/2017 2000 5/1/2017 2000 Chris 6/1/2016 5/31/2017 2000 6/1/2017 Jenna 12/1/2016 11/30/2017 2000 12/1/2016 Jenna 12/1/2016 11/30/2017 2000 1/1/2017 2000 Jenna 12/1/2016 11/30/2017 2000 2/1/2017 2000 Jenna 12/1/2016 11/30/2017 2000 3/1/2017 2000 Jenna 12/1/2016 11/30/2017 2000 4/1/2017 2000 Jenna 12/1/2016 11/30/2017 2000 5/1/2017 2000 Jenna 12/1/2016 11/30/2017 2000 6/1/2017 2000 Ilon 1/1/2017 12/31/2017 500 1/1/2017 500 Ilon 1/1/2017 12/31/2017 500 2/1/2017 500 Ilon 1/1/2017 12/31/2017 500 3/1/2017 500 Ilon 1/1/2017 12/31/2017 500 4/1/2017 500 Ilon 1/1/2017 12/31/2017 500 5/1/2017 500 Ilon 1/1/2017 12/31/2017 300 6/1/2017 300 Ilon 1/1/2017 12/31/2017 300 7/1/2017 300 Ilon 1/1/2017 12/31/2017 400 8/1/2017 400 Ilon 1/1/2017 12/31/2017 400 9/1/2017 400 Ilon 1/1/2017 12/31/2017 650 10/1/2017 650 Ilon 1/1/2017 12/31/2017 700 11/1/2017 700

result for 2017

 month result for each month 1/1/2017 4000 2/1/2017 4000 3/1/2017 4000 4/1/2017 4000 5/1/2017 4500 6/1/2017 2300 7/1/2017 300 8/1/2017 400 9/1/2017 400 10/1/2017 650 11/1/2017 700 12/1/2017 0

I've created measure for calculation such sum, which does not work well, could you please help me correct it or provide better solution.

`test2 = CALCULATE(SUM('table1'[amount]),         FILTER('table1','table1'[tets_end date] <= DATE(2017,12,1)),          FILTER('table1', 'table1'[tets_end date] >= DATE(2017,1,1)))`

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Member

## Re: sum amounts if end date is in next year

Hi all,

Found other solution (calculated column):

`Column = IF(AND(Table1[amount_date] >= DATE(Table1[year-amount_date],1,1),           AND(Table1[amount_date] <= DATE(Table1[year-amount_date],12,31),              Table1[amount_date] < Table1[end date])),Table1[amount],0)`

2 REPLIES 2
Highlighted
Member

## Re: sum amounts if end date is in next year

Hi all,

Found other solution (calculated column):

`Column = IF(AND(Table1[amount_date] >= DATE(Table1[year-amount_date],1,1),           AND(Table1[amount_date] <= DATE(Table1[year-amount_date],12,31),              Table1[amount_date] < Table1[end date])),Table1[amount],0)`

Community Support Team

## Re: sum amounts if end date is in next year

By the way, to help close this thread, just accept the solution above. Your contribution is highly appreciated.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.