How to maintain KPI Target in Power BI

[Anonymous]

Hi,

I started with Power BI last year so my target table has only 1 year of target data for each country as shown below.  This year I need to add target data for year 2023. Same as year 2022, it will be one yearly average value per country with no breakdown by month as the target is the same for every month. I have a calendar table and plan to add a slicer for year selection in every page of the visuals so that I can see the actual vs target for each selected year. 

In order to link the Target table to my calendar, do I have to add a "Date" column to the Target table and replicate the value for the 12 months of each year? Is there a quicker or better option (using DAX measure) to retrieve the selected year target value for comparison with actuals?

[Anonymous]

I managed to make the solution suggested by DataInsights work for my card visual by adding ALLEXCEPT to the calculate measure used in the visual which is as follows:

CALCULATE(SUM('3_INV Target'[INV Target]), ALLEXCEPT('2_Calendar', '2_Calendar'[Year]))

I will just input my yearly target value on 1st day of every year and do not have to replicate for every month throughout the year. Thank you for the solution.

[DataInsights · Accepted solution]

@Anonymous,

You can add a date column to the Target table and create a relationship to the calendar table using the date column. This date column will contain the first day of the year:

2022 --> 2022-01-01

2023 --> 2023-01-01

[Anonymous]

I have done that initially but the target value in the card visual for comparison went blank when the selected year in the slicer is 2022 and the year to date month is Dec. 

[DataInsights]

@Anonymous,

Does your date slicer use Year from the Calendar table? Can you provide an example pbix using OneDrive, etc.? Also attach a screenshot of the expected result.