Calculate score for each day

Hi swestendorp

If I understand correctly, what you want is:

Number of appraisals that took place (started) in the past AND will expire in the future

divided by

Number of appraisals that took place (started) in the past

with the date determining the frontier between past and future LASTDATE (period selected). If this is correct, then:

1. Create a relationship between 'Date'[Date] and 'Annual appraisal table'[Appraisal Date]

2. Create a measure like the one below.

3. Set the columns you're interested in from 'Date' (probably the months) on the rows of a matrix and use the measure

Note: I have not tested it as i do not have your sample data but see if this can help. Attaching a sample data model on top of any pictures when explaining what your issue is makes things easier for people trying to help.

DIVIDE (
    COUNTROWS (
        FILTER (
            CALCULATETABLE (
                'Annual appraisal table',
                FILTER ( ALL ( 'Date' ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
            ),
            'Annual appraisal table'[ExpiryDate] >= MAX ( 'Date table'[Date] )
        )
    ),
    COUNTROWS (
        CALCULATETABLE (
            'Annual appraisal table',
            FILTER ( ALL ( 'Date' ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
        )
    )
)