Calculate score for each day

[swestendorp]

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!

[Greg_Deckler]

From the data you provided, wouldn't there be 3? bbb, ccc and eee?

[swestendorp]

Yes, 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 · Accepted solution]

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] ) )
        )
    )
)

[swestendorp]

Hi @AlB you are AWESOME! This did the trick, thanks a lot for your support!!

[AlB]

@swestendorp

My pleasure

[swestendorp]

Hi @AIB, 

Happy new year! 

Your solution works perfectly, but could you also help me out with transforming this measure into a rolling 12 month score? 

Much appreciated!

Thanks,

Sifra

[AlB]

Hi @swestendorp

A fantastic new year to you too!!

Can you try to explain what you need exactly a bit more?

[swestendorp]

Of 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? 

[AlB]

@swestendorp

Hmmm... I don't see anything wrong with your code. What field are you placing on the x-axis of the chart? 

[swestendorp]

@AlB

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]

@swestendorp

I'll try to have a look later on. Can you share the pbix?

[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]

@swestendorp

Cool. Well done