Forum Discussion
Dynamically link earned premiums table to policy table
Try this new file which is grouped by month:
https://1drv.ms/u/s!AiiWkkwHZChHjylF131vK9FcAbY9
The new grouped table can be generated like this:
PremiumsMonthly =
SELECTCOLUMNS (
ADDCOLUMNS (
GENERATE (
Table1,
VAR PolStart = Table1[policystart]
VAR PolEnd = Table1[policyend]
RETURN
GENERATESERIES (
1,
DATEDIFF ( EOMONTH ( PolStart, 0 ), EOMONTH ( PolEnd, 0 ), MONTH ) + 1
)
),
"Days", 1
+ MIN ( Table1[policyend], EOMONTH ( Table1[policystart], [Value] - 1 ) )
- MAX ( EOMONTH ( Table1[policystart], [Value] - 2 ) + 1, Table1[policystart] ),
"Month", EOMONTH ( Table1[policystart], [Value] - 1 )
),
"PolicyNumber", Table1[PolicyNumber],
"Premium", Table1[PremiumPerDay] * [Days],
"VehicleType", Table1[Vehicle Type],
"MonthDate", [Month],
"Days", [Days]
)
Solution SageHi thanks for this it is an interesting solution but will be extremely memory intensive for lots of policies. Is there a way of doing this without adding extra rows for each day?
Thanks
- LivioLanzo
the table I am creating could be further aggregated by vehicle type so that the number of rows is drastically reduced. Could you upload a dataset including a bit more policies / vehicles types?
This wouldn't help much as I have more factors than just vehicle type so table will get exponentially bigger with each added factor.
Perhaps it is just better to earn by month only so that the table won't get too big.
Is it possible to use GENERATE to get the months between start and end date rather than like you did with CALENDAR which gets all the days.
- Ashish_Mathur
Super User
Hi,
On the x axis of the graph, will you always want to show Months only?
Would want to be able to show everything but I guess I'd hardly use days so months would be a good second best
