Target change during the year

[JBESRI]

Hi all!

I'm looking for a solution for a problem I have. We are writing all work hours and some of those hours are for our customers. Some collagues have a target on how many % of their written hours should be for a specific type work for customers.

This target can be changed during the year. see below:

The hours are calculated from a simple table with type of work, hours, name etc. But how can I measure the targets over the period of their target and combine them.

[JBESRI · Accepted solution]

Above didn't help, but I found a solution.

Made a table out of all the worked hours by day and employee, So i have in the columns Employee, day and in other columns for each type of work a column (pivot column). From there i needed the dax formula to retrieve the target by day.

target =

VAR CurrentDate = worked hours[date]

RETURN

    CALCULATE (

        MAX ( Targettable ),

        FILTER (

            'AA targettest',

            'AA targettest'[Column1] = worked hours[Employee] &&

            'AA targettest'[Startdate] <= CurrentDate &&

            'AA targettest'[Enddate] >= CurrentDate

        )

    )

[Anonymous]

Thank you @johnbasha33  for your prompt reply.

Hi @JBESRI  ,
May I ask if you have tried the suggestions above? If the suggestion helped you, you might consider marking it as a solution. If the problem has not yet been solved, please feel free to ask us a question.

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

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

[johnbasha33]

@JBESRI 

To measure the targets over the period of their target and combine them, you can follow these steps in Power BI:

1. **Create a Calendar Table**: If you haven't already, create a calendar table in Power BI that spans the period of your targets. This table will have a date column that you can use to filter and aggregate data over time.

2. **Calculate Total Hours for Each Employee and Work Type**: Write DAX measures to calculate the total hours worked by each employee for each work type. Here's an example:

```DAX
Total Hours = SUM('YourTable'[Hours])
```

Replace `'YourTable'` with the name of your table containing the work hours data.

3. **Calculate Target Hours for Each Employee and Work Type**: Write DAX measures to calculate the target hours for each employee and work type based on the target percentages. Here's an example:

```DAX
Target Hours =
VAR TargetPercentage = SELECTEDVALUE('Targets'[Target Percentage])
RETURN
TargetPercentage * [Total Hours]
```

Replace `'Targets'` with the name of your table containing the target percentages.

4. **Compare Actual vs. Target**: Write DAX measures to compare the actual hours worked to the target hours for each employee and work type. Here's an example:

```DAX
Actual vs. Target =
VAR ActualHours = [Total Hours]
VAR TargetHours = [Target Hours]
RETURN
IF(ActualHours >= TargetHours, "Met", "Not Met")
```

5. **Visualize the Data**: Create visuals in Power BI, such as tables or charts, to visualize the actual vs. target hours for each employee and work type over time. You can use slicers or filters to analyze the data for specific time periods or employees.

By following these steps, you can effectively measure the targets over the period of their target and combine them in Power BI. Adjust the DAX measures and visuals as needed to fit your specific requirements and data model.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!