cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

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

 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

2 REPLIES 2
Microsoft

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

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

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

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

Announcements