Forum Discussion
Sum between two date columns 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!!
Hi Ram_DCT ,
Here are the steps you can follow：
1. Create calculated table.
DateTable= CALENDARAUTO()2. Create measure.
Measure = var _select=SELECTEDVALUE('DateTable'[Date].[QuarterNo]) return CALCULATE(SUM('Table'[Cost]),FILTER(ALL('Table'),MAX('Table'[report_month])>=MAX('Table'[Initial_Investment_Date])&&QUARTER(MAX('Table'[report_month]))<=_select))3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
1 Reply
Hi Ram_DCT ,
Here are the steps you can follow：
1. Create calculated table.
DateTable= CALENDARAUTO()2. Create measure.
Measure = var _select=SELECTEDVALUE('DateTable'[Date].[QuarterNo]) return CALCULATE(SUM('Table'[Cost]),FILTER(ALL('Table'),MAX('Table'[report_month])>=MAX('Table'[Initial_Investment_Date])&&QUARTER(MAX('Table'[report_month]))<=_select))3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly