Forum Discussion
Dynamic measure with selected date range
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.
3 Replies
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.
Got the solution. Thank you so much!
