Forum Discussion
Sum between two dates with conditional start and end date
Hi Team,
I need a quick help - Below is my sample data;
| asset_id | Cost | report_month | Initial_Investment_Date |
| 10001 | -5657238.52 | 31 December 2018 | 20 December 2018 |
| 10002 | 1416485.97 | 13 June 2019 | 20 December 2018 |
| 10003 | -140632.97 | 31 December 2018 | 17 December 2018 |
| 10004 | -164934.35 | 31 December 2018 | 17 December 2018 |
| 10005 | -196886.16 | 31 December 2018 | 17 December 2018 |
| 10006 | -281265.94 | 31 December 2018 | 17 December 2018 |
| 10007 | -840321.12 | 31 December 2018 | 20 December 2018 |
| 10008 | -379857.57 | 13 June 2019 | 20 December 2018 |
| 10009 | -843797.83 | 31 December 2018 | 17 December 2018 |
| 10010 | -1490709.5 | 31 December 2018 | 17 December 2018 |
| 10011 | -1503310.22 | 31 December 2018 | 17 December 2018 |
| 10012 | -1828228.64 | 31 December 2018 | 17 December 2018 |
| 10013 | -1879448.61 | 31 December 2018 | 17 December 2018 |
| 10014 | -2630956.75 | 31 December 2018 | 17 December 2018 |
| 10015 | -2812659.45 | 31 December 2018 | 17 December 2018 |
| 10016 | -1969428.3 | 13 June 2019 | 12 June 2019 |
I need to get sum of the cost when;
1. report_month date >= initial_investment_date &&
2. report_month_quarter<= Selected - report_month_quarter
My report slicer is "report_month" as quarter and year. Hence, whenever I select my quarter of a year in slicer - I should get sum between above two condition 1 & 2.
Please help!!
Thanks,
Ram
Hi Anonymous ,
Refer the measure.
Measure = CALCULATE(SUM('Table'[Cost]),FILTER(ALL('Table'),'Table'[report_month]>='Table'[Initial_Investment_Date]&&'Table'[report_month].[Year]=SELECTEDVALUE('Table'[report_month].[Year])&&'Table'[report_month].[Quarter]<=SELECTEDVALUE('Table'[report_month].[Quarter])))Best Regards,
Jay
2 Replies
Hi Anonymous ,
Refer the measure.
Measure = CALCULATE(SUM('Table'[Cost]),FILTER(ALL('Table'),'Table'[report_month]>='Table'[Initial_Investment_Date]&&'Table'[report_month].[Year]=SELECTEDVALUE('Table'[report_month].[Year])&&'Table'[report_month].[Quarter]<=SELECTEDVALUE('Table'[report_month].[Quarter])))Best Regards,
Jay