Dynamic measure with selected date range

[h11]

Hi everyone,

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 need to create 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.

Currently, I have a static measure to show the target line on the chart, but I need a dynamic line that adjusts its values based on the selected date range.

Here is the screenshot for your reference.

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

I request to attach the Power BI file that you worked on using my data as a reference. Thank you in advance. 

[h11]

Got the solution. Thank you so much! 

[parry2k · Accepted solution]

@h11 you can check number of months selected and then multiple by 10

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

[h11]

Hi,

Thank you so much for providing a solution to my question. It solved 80% of my problem. However, the measure that you shared currently only adds the target if there are projects in that month. I need to add an equal target for everyone. For example, the target should be 10 for the first month, 20 for the second month, and 30 for the third month, creating a straight line for everyone. Could you please help me create a measure to achieve this?

Please look at the below screenshot for reference:

The target of taylor is only 10 since he has only a project in the month of Oct. But ideally he should have the same target as 30 if I choose the filter from Oct to Dec. 

I hope you understood my request. Please help me.