Compare with Monthly and Daily Target

[Junaid11]

Hello,
I have two tables. One table gives detail of Daily and monthly target for each month and category for a user of tasks completed which is given below:

Month/Year	User Name	Daily Target	Monthly Target	Category
Nov-22	James	234	2355	Service
Nov-22	John	567	5576	Asset
Nov-22	James	235	6863	Manufacture
Nov-22	John	676	6828	Service

The second table is giving the details of each day task completed by each user in their respective category.
I want to count the number of task for each day and month from second table and compare them with first table daily and monthly tasks. I want to get the percentage like if as per 2nd table John completed 48 tasks in  Service category so what should be the % achieved for daily similarly for monthly. Second table is below:

Task	User	Date	Time	Category
AB	James	11/5/2022	12:35:00 PM	Service
BC	John	11/5/2022	6:39:00 PM	Manufacture
CD	James	12/5/2022	1:02:00 AM	Asset
RG	James	13/5/2022	12:00:00 AM	Service
YI	John	12/5/2022	3:45:00 AM	Service
WR	James	11/5/2022	10:35:00 PM	Manufacture
UIO	John	11/5/2022	2:09:00 PM	Manufacture

Kindly let me knwo should I make data model based on user name?
Please help me understand the DAX function to bemade aling with data model.
Thank you

[v-yanjiang-msft · Accepted solution]

Hi @Junaid11 ,

According to your description, here's my solution.

Sample:

Target table:

Table:

Note: Month/Year column in Target table is date type, but MonthYear column in Table is text type.

Here's my solution, create two measures:

DailyTargetPercentage =
VAR _Compelete =
    COUNTROWS ( 'Table' )
VAR _Target =
    MAXX (
        FILTER (
            'Target',
            EOMONTH ( 'Target'[Month/Year], 0 ) = EOMONTH ( MAX ( 'Table'[Date] ), 0 )
                && 'Target'[User Name] = MAX ( 'Table'[User] )
                && 'Target'[Category] = MAX ( 'Table'[Category] )
        ),
        'Target'[Daily Target]
    )
RETURN
    DIVIDE ( _Compelete, _Target )

MonthlyTargetPercentage =
VAR _Compelete =
    COUNTROWS ( 'Table' )
VAR _Target =
    MAXX (
        FILTER (
            'Target',
            EOMONTH ( 'Target'[Month/Year], 0 ) = EOMONTH ( MAX ( 'Table'[Date] ), 0 )
                && 'Target'[User Name] = MAX ( 'Table'[User] )
                && 'Target'[Category] = MAX ( 'Table'[Category] )
        ),
        'Target'[Monthly Target]
    )
RETURN
    DIVIDE ( _Compelete, _Target )

Get the result:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.