Forum Discussion
Helper ICalculate score for each day
Good day,
I have two tables containing (1) employeeNo, appraisal date and expiry date of the appraisal and (2) a date table. I want to visualize the rolling score of how many employees have a valid appraisal (expiry date <= today) at a given date (visualized in a graph). There might be an easy solution, but I can't wrap my head around it.
I tried something like this, but it returns an error because I used LASTDATE as a true/false expression in a table filter expression which is not allowed
Valid appraisals =
CALCULATE (
COUNTROWS ( 'Annual appraisal table' ),
'Annual appraisal table'[ExpiryDate] >= LASTDATE ( 'Date table'[Date] )
)
/ CALCULATE (
COUNTROWS ( 'Annual appraisal table' ),
LASTDATE ( 'Date table'[Date] )
)
I guess there needs to be some kind of relationship between this table and the date table? I am not sure which colums to link.
As an example, looking at below table, today I'd have 2 valid appraisals against a total of 6 appraisals = a 33% score, whereas in September I'd have had 3 valid appraisal = a 50% score. Null should count as expired.
| EmployeeNo | Appraisal date | Expiry date |
| aaa | 7-7-2018 | 7-7-2019 |
| bbb | 28-9-2015 | 28-9-2016 |
| ccc | 19-9-2017 | 19-9-2018 |
| ddd | null | null |
| eee | 23-10-2017 | 23-10-2018 |
| fff | 29-12-2017 | 29-12-2018 |
Pleased to hear how you think I should solve this. Thanks a lot in advance!
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] ) )
)
)
)
13 Replies
- Greg_Deckler
Community Champion
From the data you provided, wouldn't there be 3? bbb, ccc and eee?
swestendorpYes, you are right, my bad. I was thinking of the last day of September, which then another one would have expired. Been Power BI'ing all day, not very sharp anymore!
Apart from that detail, was my question clear enough? Was a bit struggling with how to put in words :)
Thanks for your help!
- AlB
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] ) )
)
)
)
