Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello, this is a simplified version of what i'm trying to acheive:
In insurance you "earn" a premium proportionately over the lifetime of the policy.
Our policyFact table has a row for each policy with the WrittenPremium, StartDate, EndDate, etc.
To calculate the earnings as at today (valuation date) I have added a column with this code (pseudo):
Earnings =
[Prem] *
DIVIDE(
(DATEDIFF([StartDate], [ValuationDate],Day)+1),
(DATEDIFF([StartDate], [EndDate],Day)+1)
)
Where [ValuationDate] is a measure: =today()
This essentially multiplies the Premium by a fraction depending on how many days into the policy it is.
I have Measure which then sums this column called Total Earnings.
In my DateTable, I want to be able to be able to display the Total Earnings as at each date in the table, so [ValuationDate] becomes a variable. Does anyone know how I go about this please?
Many thanks for your help!
Solved! Go to Solution.
Hi @PowerPaddy,
According to your description above, you should be able to use the formula below to create a new measure to calculate the Total Earnings for each date in the date table, and show it with the Date column of DateTable on Chart on the report in your scenario.
Measure For Total Earnings = VAR currentDate = MAX ( DateTable[Date] ) RETURN SUMX ( policyFact, IF ( currentDate >= [StartDate], [Prem] * DIVIDE ( ( DATEDIFF ( [StartDate], currentDate, DAY ) + 1 ), ( DATEDIFF ( [StartDate], currentDate, DAY ) + 1 ) ) ) )
Regards
Hi @PowerPaddy,
Great to hear the problem got resolved! Could you accept my reply above as solution to close this thread?
Regards
Hi @PowerPaddy,
According to your description above, you should be able to use the formula below to create a new measure to calculate the Total Earnings for each date in the date table, and show it with the Date column of DateTable on Chart on the report in your scenario.
Measure For Total Earnings = VAR currentDate = MAX ( DateTable[Date] ) RETURN SUMX ( policyFact, IF ( currentDate >= [StartDate], [Prem] * DIVIDE ( ( DATEDIFF ( [StartDate], currentDate, DAY ) + 1 ), ( DATEDIFF ( [StartDate], currentDate, DAY ) + 1 ) ) ) )
Regards
Thanks very much, worked a treat!
Looks pretty obvious once you know how.
Hi @PowerPaddy,
Great to hear the problem got resolved! Could you accept my reply above as solution to close this thread?
Regards
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
106 | |
86 | |
77 | |
69 |
User | Count |
---|---|
120 | |
112 | |
94 | |
84 | |
75 |