Forum Discussion

PowerPaddy's avatar
PowerPaddy
Frequent Visitor
9 years ago
Solved

Variable Date Measure - Insurance


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!

Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    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. :smileyhappy:

    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's avatar
    v-ljerr-msft
    9 years ago

    Hi PowerPaddy,

     

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

     

    Regards

3 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    9 years ago

    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. :smileyhappy:

    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's avatar
      PowerPaddy
      Frequent Visitor
      9 years ago

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

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Microsoft Employee
        9 years ago

        Hi PowerPaddy,

         

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

         

        Regards