Add parameter to Highest and Lowest value in Matrix and Year format

[bimystic]

I am using BI to show the below KPI chart below 

Figure 1: Power BI Report

The raw data is available as below and I am converting it for using in BI 

Table A: Raw Data

KPI	Priority	Target	Minimum	Aug 2017	Sep 2017	Oct 2017	Nov 2017	Dec 2017	Jan 2018	Feb 2018	Mar 2018	Apr 2018	May 2018	Jun 2018
My Project KPI	P1	>=98%	<85%	100	91	98	100	100	92	100	94	100	89	100
P2	>=95%	<70%	89	92	96	93	98	94	99	100	99	100	99
P3	>=90%	<50%	98	100	100	88	100	100	80	100	100	99	100

The transposed data used by me is as shown below:

Table B: Data format used in Power BI

Priority	Date	KPI_Value
P1	1-Jan-19	98
P2	1-Jan-19	92
P3	1-Jan-19	80
P1	1-Feb-19	60
P2	1-Feb-19	100
P3	1-Feb-19	74
P1	1-Mar-19	100
P2	1-Mar-19	99
P3	1-Mar-19	82
P1	1-Apr-19	98
P2	1-Apr-19	94
P3	1-Apr-19	100

I have used Matrix report and created the above report. 

Currently I have to ensure the below:

1- In order to highlight the values which are outside the KPI I have used Field Formatting -> Advanced Settings and set the upper and lower limits. So you can see in my graph that all values below 80 are in RED and in 100 are in green.

The problem is that I have a different threshold for each row as shown in Table A. Can I use some parameters or provide some tool to enable the user to change the upper and lower limit and see the color being updated in the graph. Like we have the slicer and we can choose and filter records.

2-Date Filter 

I need to add a filter in the Slicer which needs to display only the year as the company has data for last 6 years. Currently, when I am adding a date filter it is showing up the dates as showing in the Figure 1 at the top. Do I need to do some changes in the data model to display only Year in the slicer?

Thank you in advance for your help.

Regards

BI Mystic 

[v-chuncz-msft · Accepted solution]

@bimystic,

You can apply conditional formatting based on a measure and use YEAR to add a calculated column for slicer.

[bimystic]

Thanks for your reply, I am using a new column and adding year, how can I set it to pick the date from my column having dates.

Is there a tutorial already available for the measure and conditional formatting and how to use an existing column to calculate values in the new column. Sorry for my queries as I am a newbie. 

Regards