Forum Discussion
Helper ICalculate 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] ) )
)
)
)
Helper I
I use my date table on the x-axis, which i've linked to the table using the 'LatestOBDate' column within the table.
When I use the 'LastFrom[Board]' column, the graph looks fine. This is not correct however, as I don't have a date for each row. Will those row be included in the calculation either way if I use the 'LastFrom[Board]' column to link my date table?
Community Champion- swestendorp
Hi AlB Thanks again for helping me out, but I think I have fixed it. I have changed the last part of the code to "
&& 'Date table'[Date] < MAX( 'Date table'[Date] " i/o "&& 'Date table'[Date] <= MAX( 'Date table'[Date]". - AlB
Cool. Well done