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] ) )
)
)
)
Community ChampionHi swestendorp
A fantastic new year to you too!! :smileyhappy:
Can you try to explain what you need exactly a bit more?
Helper IOf course :)
I used your solution for another case. I want to be able to calculate a score basis a rolling year, so for every max on my X-axis, I want to take into a account 12 months of data. When I try to re-write the statement to a 12 month rolling score, I get below result. I guess I am doing something wrong, but I can't fix it
Syntax I used looks as follows:
Term Performance RY = DIVIDE (
COUNTROWS (
FILTER (
CALCULATETABLE (
'Terms on board',
FILTER ( ALL ( 'Date table' ), 'Date table'[Date] >= MAX('Date table'[Date]) - (365) && 'Date table'[Date] <= MAX ( 'Date table'[Date] ) )
),
'Terms on board'[Within target] = "WITHIN TARGET" )
)
,
COUNTROWS (
CALCULATETABLE (
'Terms on board',
FILTER ( ALL ( 'Date table' ), 'Date table'[Date] >= MAX('Date table'[Date]) - (365) && 'Date table'[Date] <= MAX ( 'Date table'[Date] ) )
)
)
)I don't understand why the result drops in 2019. What am I doing wrong?
- 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
Hmmm... I don't see anything wrong with your code. What field are you placing on the x-axis of the chart?
- swestendorp
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?
- AlB
- AlB
Cool. Well done