Sum between two date columns with conditional start and end date

[Ram_DCT]

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!!

[v-yangliu-msft · Accepted solution]

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