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]
)
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.
Solution Sage
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]
)