cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
bsas Member
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:

 

namestrat dateend dateamountamount_datewhat we should sum in 2017
Chris6/1/20165/31/201710006/1/2016 
Chris6/1/20165/31/201710007/1/2016 
Chris6/1/20165/31/201710008/1/2016 
Chris6/1/20165/31/201710009/1/2016 
Chris6/1/20165/31/2017150010/1/2016 
Chris6/1/20165/31/2017150011/1/2016 
Chris6/1/20165/31/2017150012/1/2016 
Chris6/1/20165/31/201715001/1/20171500
Chris6/1/20165/31/201715002/1/20171500
Chris6/1/20165/31/201715003/1/20171500
Chris6/1/20165/31/201715004/1/20171500
Chris6/1/20165/31/201720005/1/20172000
Chris6/1/20165/31/201720006/1/2017 
Jenna12/1/201611/30/2017200012/1/2016 
Jenna12/1/201611/30/201720001/1/20172000
Jenna12/1/201611/30/201720002/1/20172000
Jenna12/1/201611/30/201720003/1/20172000
Jenna12/1/201611/30/201720004/1/20172000
Jenna12/1/201611/30/201720005/1/20172000
Jenna12/1/201611/30/201720006/1/20172000
Ilon1/1/201712/31/20175001/1/2017500
Ilon1/1/201712/31/20175002/1/2017500
Ilon1/1/201712/31/20175003/1/2017500
Ilon1/1/201712/31/20175004/1/2017500
Ilon1/1/201712/31/20175005/1/2017500
Ilon1/1/201712/31/20173006/1/2017300
Ilon1/1/201712/31/20173007/1/2017300
Ilon1/1/201712/31/20174008/1/2017400
Ilon1/1/201712/31/20174009/1/2017400
Ilon1/1/201712/31/201765010/1/2017650
Ilon1/1/201712/31/201770011/1/2017700

 

result for 2017

 

monthresult for each month
1/1/20174000
2/1/20174000
3/1/20174000
4/1/20174000
5/1/20174500
6/1/20172300
7/1/2017300
8/1/2017400
9/1/2017400
10/1/2017650
11/1/2017700
12/1/20170

 

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
bsas Member
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
bsas Member
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
Community Support Team

Re: sum amounts if end date is in next year

@bsas,

 

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.