I have a table in which I have one column "Status" it is a string column containing 2 categorical values "Achieved" and "Not-Achieved" based on which I have calculated the count and percentage for both the categories. I want created a target line for upper and lower limit. I am displaying the data in month day wise format.
LOGIC: My logic for creating target line is that I want to check the top 3 values of each month here I have to consider 3 months for calculation. In the top 3 values of each month which is the lowest i.e the target line should cross atleast top 3 values of each month. And when new month is added it should take the new month data to calculate and not consider previous month.
FOR EXAMPLE: I have data from Aug to Dec and now Jan also I have started to add in it. To calculate the Target Line I will check the top 3 percentage in Aug, Sep, and Oct total 9 values and set the target line which will cross the 9 values in the chart. As now I have added Jan data the target line should consider Nov, Dec, and Jan values to set a new target.
Currently I have add target line manually:
Sample data:
Hi @sumeetkr ,
According to your description, here are my steps you can follow as a solution.
(1) This is my test data.
(2) We can create a measure.
target =
var _total=9
var _max_date=CALCULATE(MAX('Table'[Physical Vehicle Delivered Time]),ALLSELECTED('Table'))
var _max_date_1=DATE(YEAR(_max_date),MONTH(_max_date),1)
var _max_date_2=DATE(YEAR(EDATE(_max_date,-1)),MONTH(EDATE(_max_date,-1)),1)
var _max_date_3=DATE(YEAR(EDATE(_max_date,-2)),MONTH(EDATE(_max_date,-2)),1)
var _KPI_Yes1 = COUNTROWS(FILTER(ALLSELECTED('Table'),[KPI Status]="Achieved"&& [Physical Vehicle Delivered Time]>=_max_date_1 && [Physical Vehicle Delivered Time]<=_max_date_1+2))
var _KPI_Yes2 = COUNTROWS(FILTER(ALLSELECTED('Table'),[KPI Status]="Achieved"&& [Physical Vehicle Delivered Time]>=_max_date_2 && [Physical Vehicle Delivered Time]<=_max_date_2+2))
var _KPI_Yes3 = COUNTROWS(FILTER(ALLSELECTED('Table'),[KPI Status]="Achieved"&& [Physical Vehicle Delivered Time]>=_max_date_3 && [Physical Vehicle Delivered Time]<=_max_date_3+2))
RETURN (_KPI_Yes1+_KPI_Yes2+_KPI_Yes3)/_totalIn my test table, January 2025 is the most recent data, so the calculations are for attainment from the 1st through the 3rd of each month for January 2025, December 2024, and November 2024, and based on your description of three months and three dates then the total is 9.
(3) Then the result is as follows.
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @sumeetkr ,
According to your description, here are my steps you can follow as a solution.
(1) This is my test data.
(2) We can create a measure.
target =
var _total=9
var _max_date=CALCULATE(MAX('Table'[Physical Vehicle Delivered Time]),ALLSELECTED('Table'))
var _max_date_1=DATE(YEAR(_max_date),MONTH(_max_date),1)
var _max_date_2=DATE(YEAR(EDATE(_max_date,-1)),MONTH(EDATE(_max_date,-1)),1)
var _max_date_3=DATE(YEAR(EDATE(_max_date,-2)),MONTH(EDATE(_max_date,-2)),1)
var _KPI_Yes1 = COUNTROWS(FILTER(ALLSELECTED('Table'),[KPI Status]="Achieved"&& [Physical Vehicle Delivered Time]>=_max_date_1 && [Physical Vehicle Delivered Time]<=_max_date_1+2))
var _KPI_Yes2 = COUNTROWS(FILTER(ALLSELECTED('Table'),[KPI Status]="Achieved"&& [Physical Vehicle Delivered Time]>=_max_date_2 && [Physical Vehicle Delivered Time]<=_max_date_2+2))
var _KPI_Yes3 = COUNTROWS(FILTER(ALLSELECTED('Table'),[KPI Status]="Achieved"&& [Physical Vehicle Delivered Time]>=_max_date_3 && [Physical Vehicle Delivered Time]<=_max_date_3+2))
RETURN (_KPI_Yes1+_KPI_Yes2+_KPI_Yes3)/_totalIn my test table, January 2025 is the most recent data, so the calculations are for attainment from the 1st through the 3rd of each month for January 2025, December 2024, and November 2024, and based on your description of three months and three dates then the total is 9.
(3) Then the result is as follows.
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helpful resources
Power BI Dataviz World Championships
The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now!
Power BI Monthly Update - November 2025
Check out the November 2025 Power BI update to learn about new features.
FabCon Atlanta 2026
Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
