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,
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
Hi,
Though i have not tried an alternative solution diligently, my guess is that the DAX formula for that would be very intensive (if we do not break out the table into individual date rows). This is because we would have to create an inactive relationship between policyend date and the Date column of the Calendae Table. Furthermore, by breaking out the Table into individual date wise rows, we can use other Date/Time intelligence functions.
I tried to create one row per month instead of one row per date but then the problem is that if we assign the first date of the month to the row, then we are assuming that the plicy start date is the first date (which in many cases will not be true).
Sorry but cannot help much here.
