Measure to show same targets regardless of capacity

[h11]

Hi Team,

I have data represented in a line and clustered column chart that shows the capacity of each user. The X-axis displays the ppl names, and the Y-axis shows the number of projects assigned. We have a target of 10 projects per month per person.

I created a dynamic measure or a calculated column on the line Y-axis to reflect this target. For example, if the date range is set to one month (October 1 to October 31), the target should be 10. If the date range is 2 months (October 1 to November 30), the target should be 20 (10 projects per month), and so on for up to six months.

The below is the measure:

Target =
VAR __FixedValue = 10
VAR __MinDate = MIN ( 'Calendar'[Date] )
VAR __MaxDate = MAX ( 'Calendar'[Date] )
VAR __MonthsSelected = DATEDIFF ( __MinDate, __MaxDate, MONTH ) + 1
RETURN
__FixedValue * __MonthsSelected

Here is the data:

Project Title	Name	Date
1	Bhaskar	29-Nov-24
2	Vicky	20-Dec-24
3	Bhaskar	20-Dec-24
4	Bhaskar	20-Dec-24
5	David	31-Oct-24
6	Anil	31-Oct-24
7	Steve	31-Oct-24
8	Bhaskar	31-Oct-24
9	Bhaskar	31-Oct-24
10	Taylor	31-Oct-24
11	Anil	31-Oct-24
12	Anil	31-Oct-24
13	Anil	1-Nov-24
14	Anil	1-Dec-24
15	Steve	1-Nov-24
16	Bhaskar	1-Dec-24
17	Bhaskar	11-Oct-24
18	Bhaskar	11-Oct-24
19	Bhaskar	1-Nov-24
20	Anil	1-Dec-24
21	Anil	11-Oct-24
22	Anil	11-Oct-24
23	Anil	1-Nov-24
24	Steve	1-Dec-24
25	Steve	11-Oct-24
26	Steve	11-Oct-24
27	Steve	11-Oct-24
28	Steve	11-Oct-24
29	Steve	15-Nov-24
30	David	15-Nov-24
31	David	15-Nov-24
32	David	2-Dec-24
33	David	2-Dec-24
34	David	2-Dec-24
35	David	31-Oct-24
36	David	31-Oct-24
37	David	31-Oct-24
38	David	15-Nov-24
39	Vicky	15-Nov-24
40	Vicky	15-Nov-24
41	Vicky	31-Oct-24
42	Vicky	31-Oct-24

Everything is fine, but I need to set an equal target for everyone. For instance, the target should be 10 if I select 1 month, 20 for the second month, and 30 for the third month, regardless of capacity, creating a straight line for everyone. This way, I can show that the target is the same for all individuals, allowing me to reassign projects from those who are over capacity to those who are under capacity.

Thank you!

@SamWiseOwl @parry2k @rajendraongole1 @Kedar_Pande @Ritaf1983 @lbendlin 

[grazitti_sapna]

Hi @h11 ,

Yes, the approach you are using will give you the desired result as long as the target is calculated dynamically based on the selected date range. The measure you’ve created will indeed give a consistent target for everyone based on the number of months selected.

Dax:

• Target =
  VAR __FixedValue = 10
  VAR __MinDate = MIN('Calendar'[Date])
  VAR __MaxDate = MAX('Calendar'[Date])
  VAR __MonthsSelected = DATEDIFF(__MinDate, __MaxDate, MONTH) + 1
  RETURN
  __FixedValue * __MonthsSelected
• It calculates the difference in months between the minimum and maximum dates in the selected date range.
• It multiplies this number by the fixed value of 10 (your monthly target), ensuring that each month adds an additional 10 projects to the target.

By placing this measure on the line Y-axis, the chart should display a horizontal line at the same level for each person, representing the target over the selected period. This will enable you to easily identify who is over or under capacity, allowing you to reassign projects accordingly.

If I have resolved your question, please consider marking my post as a solution🎉. Thank you!

[h11]

Hi @grazitti_sapna 

Thank you for your response. 

I used the same measure, but the target I’m getting isn’t consistent for everyone. For example, Taylor has 1 project in October, while Vicky has 2 projects in October and 2 projects in November. When I slice the date between October and November, the target line on the Y-axis isn’t straight but slants, showing a target of 10 for Taylor and 20 for Vicky, as illustrated in the attached picture.

[Hemansi · Accepted solution]

Hi @h11 ,

I would remove Title name filter when calculating min and max dates. You could use ALL, ALLSELECTED, REMOVEFILTER context to do this.

Target =
VAR __FixedValue = 10
VAR __MinDate = CALCULATE ( MIN ( 'Calendar'[Date] ), REMOVEFILTER('Data'[TitleName] )
VAR __MaxDate = CALCULATE ( MAX ( 'Calendar'[Date] ), REMOVEFILTER('Data'[TitleName] )
VAR __MonthsSelected = DATEDIFF ( __MinDate, __MaxDate, MONTH ) + 1
RETURN
__FixedValue * __MonthsSelected

Hope this helps.

[h11]

Hey @Hemansi I've used AllSELECTED instead of REMOVEFILTER and it worked!!!! Thank you!!

[Hemansi]

Glad it worked 🙂

[h11]

Hi  @Hemansi 

Thank you for providing the measure. I used it but it didn't work.

Please help!! 

[Hemansi]

This may probably be bacause your date table is different to your title table, which is why you cannot ammend filter context by using Removefilter.