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

     

     

AlB's avatar
AlB
Icon for Community Champion rankCommunity Champion
7 years ago

swestendorp

My pleasure :smileyhappy:

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

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

    Hi swestendorp

    A fantastic new year to you too!! :smileyhappy:

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

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

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

        swestendorp

         

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