Variable Date Measure - Insurance

[PowerPaddy]

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!

[v-ljerr-msft · Accepted 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

[v-ljerr-msft · Accepted 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

[PowerPaddy]

Thanks very much, worked a treat!
Looks pretty obvious once you know how.

[v-ljerr-msft · Accepted solution]

Hi @PowerPaddy,

Great to hear the problem got resolved! Could you accept my reply above as solution to close this thread? 

Regards