Summarize and Slice based on time period( start and end date)

[summerpolice]

Start Date	End Date	Name	Balance	Start year	End year
1/1/2014	12/31/2014	XX	100	2014	2014
1/1/2015	12/31/2015	XX	200	2015	2015
1/1/2016	12/31/2016	XX	300	2016	2016
8/1/2015	7/31/2016	YY	400	2015	2016
8/1/2016	7/31/2017	YY	500	2016	2017
8/1/2017	7/31/2018	YY	600	2017	2018

I am trying to calculate my balance exposure as of a  random date between the start and end date. Let me know if you help me in this

1. I need to create a date dimension so thatIcan pick any date and say how much is the balance of that day 

if I pick 9/1/2016 my exposure is - 300+500

2. I also a need a financial year exposure.Filter all rows with exposure in the year 2016 ( slicer by any year).In this case, the rows will be 3,4,5 as the start and end year can be in 2016 ...

3. Using the slicer( date dimension), I want to select all rows where the eg Like start date > date dimension -30 days or end date < date dimension +30 days

Any thoughts .Thanks for your help

[v-yulgu-msft]

Hi @summerpolice，

Create below measure. Dim is a calendar table which used as date dimension for users to pick up any date.

Sum balance =
CALCULATE (
SUM ( 'time period'[ Balance] ),
FILTER (
'time period',
AND (
'time period'[Start Date] <= MAXX ( ALLSELECTED ( dim[Date] ), 'dim'[Date] ),
'time period'[ End Date] >= MAXX ( ALLSELECTED ( dim[Date] ), 'dim'[Date] )
)
)
)

Thanks,
Yuliana Gu

[summerpolice]

Thanks it helps but i am still running into few issues.

If i pick a range of date in "Dim" , how do i identify all the rows in "timeperiod" which fall in the range and get a sum of balance.

The exposure could be as of  a day or for a range of days