Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
User | Count |
---|---|
85 | |
80 | |
72 | |
71 | |
55 |
User | Count |
---|---|
108 | |
106 | |
93 | |
85 | |
67 |