Forum Discussion
Measures derived from date ranges
I have a row for each policy period, which at most is an entire year. Each row has a date range, the policy period, and the % of 12 months, what I'm calling the exposure. So 1 year is 1, and 6 months is 0.5 and so on. Lastly, I have the premium and losses for that policy period.
What I'd like to be able to do, which I'm not sure is possible, is have measures for premium, losses, and exposures sliced by date range given just the policy period date range. That is, monthly premium, daily premium, or total exposures sliced by date range given only the start_date and end_date of each row. Ideally, I'll be able to have a date slicer and see the monthly or daily totals and averages as change the date range of the slicer.
The only way I can think to do this is to expand each policy period row into monthly rows, weekly rows, or daily rows to get the respective average. So a 6-month policy could be expanded into 6 rows, one for each month. But since I'm dealing with millions of rows in my dataset, I would prefer not to compound its size any further.
Is there anyway to get measures based only off date ranges?
Hi Anonymous,
Please download the demo from the attachment.
1. Create an independent date table.
2. Don't establish any relationships.
3. Create two measures.
4. One tip: you have to handle the date range in the slicer. The formula doesn't handle anything like monthly, daily.
Measure = VAR rangeStart = MIN ( 'Calendar'[Date] ) VAR rangeEnd = MAX ( 'Calendar'[Date] ) RETURN SUMX ( SUMMARIZE ( 'Table1', Table1[policy], Table1[start_date], Table1[end_date], "rowPremium", VAR rangeDays = DATEDIFF ( [start_date], [end_date], DAY ) VAR inrangeDays = IF ( Table1[start_date] >= rangeStart && rangeEnd >= Table1[start_date] && rangeEnd <= Table1[end_date], DATEDIFF ( Table1[start_date], rangeEnd, DAY ), IF ( Table1[start_date] >= rangeStart && rangeEnd >= Table1[end_date], DATEDIFF ( Table1[start_date], Table1[end_date], DAY ), IF ( Table1[start_date] <= rangeStart && rangeEnd <= Table1[end_date], DATEDIFF ( rangeStart, rangeEnd, DAY ), IF ( Table1[start_date] <= rangeStart && rangeEnd >= Table1[end_date] && rangeStart <= Table1[end_date], DATEDIFF ( rangeStart, Table1[end_date], DAY ), 0 ) ) ) ) RETURN DIVIDE ( inrangeDays, rangeDays, 0 ) * SUM ( Table1[premium] ) ), [rowPremium] )
Measure 2 = VAR rangeStart = MIN ( 'Calendar'[Date] ) VAR rangeEnd = MAX ( 'Calendar'[Date] ) RETURN SUMX ( SUMMARIZE ( 'Table1', Table1[policy], Table1[start_date], Table1[end_date], "rowExposure", VAR inrangeDays = IF ( Table1[start_date] >= rangeStart && rangeEnd >= Table1[start_date] && rangeEnd <= Table1[end_date], DATEDIFF ( Table1[start_date], rangeEnd, DAY ), IF ( Table1[start_date] >= rangeStart && rangeEnd >= Table1[end_date], DATEDIFF ( Table1[start_date], Table1[end_date], DAY ), IF ( Table1[start_date] <= rangeStart && rangeEnd <= Table1[end_date], DATEDIFF ( rangeStart, rangeEnd, DAY ), IF ( Table1[start_date] <= rangeStart && rangeEnd >= Table1[end_date] && rangeStart <= Table1[end_date], DATEDIFF ( rangeStart, Table1[end_date], DAY ), 0 ) ) ) ) RETURN DIVIDE ( inrangeDays, 365, 0 ) ), [rowExposure] )
11 Replies
- AlB
Community Champion
Hi Anonymous
Can you provide an example to clarify what you need exactly?
For instance, if the date range in the slicer slicer has, say, October 2014, what would your measures yield and why?
Thank you for the response, AlB! A simple measure I would use is total premium and exposure, sliced by date range. So, for October 2014 I would need to calculate the October 2014 premium and exposure for each row, then sum to get the total.
Using the example below: the third Policy C row has 29 days in October, 2014. So the exposure is 29/365 = 0.079. The premium for that 54 day policy period is $319, but the October portion is 29 days so the October premium is $171.31. The same logic goes for the other row with October days. Summing these two rows give the total October 2014 premium and exposure.
Ideally, I would like this kind of measure to be more flexible than just monthly, so it could work with specific date ranges, not just monthly ranges.
Thanks!
- AlB
Anonymous
Ouch. Yeah that looks quite tough. I hadn't at first realized the issue with filtering the dates. I guess you would need to structure the data in a different way but I don't think I can be of much help there. I'm curious though, how would you expand the policy periods? With DAX? Something else?
