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,
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.