Forum Discussion

Junaid11's avatar
Junaid11
Icon for Helper V rankHelper V
3 years ago
Solved

Compare with Monthly and Daily Target

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/YearUser NameDaily TargetMonthly TargetCategory
Nov-22James2342355Service
Nov-22John5675576Asset
Nov-22James2356863Manufacture
Nov-22John6766828Service

 

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:

TaskUserDateTimeCategory
ABJames11/5/202212:35:00 PMService
BCJohn11/5/20226:39:00 PMManufacture
CDJames12/5/20221:02:00 AMAsset
RGJames13/5/202212:00:00 AMService
YIJohn12/5/20223:45:00 AMService
WRJames11/5/202210:35:00 PMManufacture
UIOJohn11/5/20222:09:00 PMManufacture


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

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    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.

     

1 Reply

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    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.