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]
)
Hi 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
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.
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]
)Hi,
It looks like my statments in the previous messahe were incorrect. Without creating one row for each date, there can be a solution provided you would not want to see day wise premium collected. You may download the PBI file from here.
Hope this helps.
