Forum Discussion

OmarSaleh's avatar
OmarSaleh
Helper I
5 years ago

Calculating KPI Achievement based on two dates

Hi all 

 

I'm trying to calculate YTD visits achievment of the employees based on below tables: 

 

Employees table

NameEmployement dateTeamID
X2-Feb-2021A111
Y1-May-2021B112

 

Visits Target: 

TeamMonthTarget
AAll Months except April 5
AApril3
BAll Months except April 4
BApril2

 

Visit YTD Acivement is calculated by dividing number of YTD real visits / by YTD target visits. 

 

Can any one help with writing the function or equation that calculates this consedering the following: 

1- Above targets table per team per month (April has different target than the rest of months in the year) 

2- Employement start date, as visits target should exclude the months of the year before this date and should be calculated till the current month of the year. 

 

 

Example: 

 

Employee A joined in Feb and the current month is June. His visits target should be ---> 23 visits (3 for April, and 5 for Feb, 5 for Mar, 5 for May, and 5 for Jun) 

If his real visits number is 20 then this achievment is 20/23 = 87% 

 

Thanks a million in advance for your support. 

Let me know if you need further clarification please. 

 

Need Help
Show and Tell

5 Replies

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    5 years ago

    Hi, OmarSaleh 

    Try measure as below:

    Diff_month =
MONTH ( TODAY () )
    - MONTH ( SELECTEDVALUE ( 'Employees Table'[Employement date] ) ) + 1

    Visits target =
VAR target1 =
    CALCULATE (
        MAX ( 'Visits Target'[Target] ),
        FILTER ( 'Visits Target', 'Visits Target'[Month] = "All Months except April" )
    )
VAR target2 =
    CALCULATE (
        MAX ( 'Visits Target'[Target] ),
        FILTER ( 'Visits Target', 'Visits Target'[Month] = "April" )
    )
RETURN
    IF (
        FORMAT ( TODAY (), "mmmm" ) >= "April",
        target1 * ( [Diff_month] - 1 ) + target2,
        target1 * [Diff_month]
    )

     

    If it doesn't meet your requirement,please share your expected result in excel.

     

    Best Regards,
    Community Support Team _ Eason

    • OmarSaleh's avatar
      OmarSaleh
      Helper I
      5 years ago

      Thanks for your reply and help v-easonf-msft . Unfortunately the target numbers has been changed and the code you provided doesn't work with the new target table. 

       

      Here it is.. 

       

       

      Any advise on how to calculate the target with the same considirations above?

      • v-easonf-msft's avatar
        v-easonf-msft
        Community Support
        5 years ago

        Hi, OmarSaleh 

        You need to unpivot columns as below.

        Then try formula as below:
        Calculated column "Month.no":

         

         

        Month.no = SWITCH('Visits Target'[Month],"January",1,"February",2,"March",3,"April",4,"May",5,"June",6,"July",7,"August",8,"September",9,"October",10,"November",11,"December",12)

         

         

         measue "Visits target":

         

         

        Visits target = 
CALCULATE (
    SUM ( 'Visits Target'[Target] ),
    FILTER (
        'Visits Target',
        'Visits Target'[Team] = SELECTEDVALUE ( 'Employees Table'[Team] )
            && 'Visits Target'[Month.no] <= MONTH ( TODAY () )
            && 'Visits Target'[Month.no]
                >= MONTH ( SELECTEDVALUE ( 'Employees Table'[Employement date] ) )
    )
)

         

         

        Please check my sample  file for more details.

         

        Best Regards,
        Community Support Team _ Eason