Forum Discussion

swestendorp's avatar
swestendorp
Icon for Helper I rankHelper I
7 years ago
Solved

Calculate 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 va...
Need Help
  • AlB's avatar
    AlB
    7 years ago

    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's avatar
swestendorp
Icon for Helper I rankHelper I
7 years ago

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's avatar
AlB
Icon for Community Champion rankCommunity Champion
7 years ago

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