cancel
Showing results for 
Search instead for 
Did you mean: 
amitchandak

Power BI Distributing/Allocating the Yearly Target(Convert to Daily Target): Measure ( Daily/YTD)

Objective:

Here we have a target table provided at the year level and we would like to display the target at the day level also we would like to see YTD Targets.

 

Data:

The target table has 2 columns: Target and Year.

 

Solution:

We have added 2 new columns to the target table year start date and the year end date (Named it Date). As we are using the calendar year, we have hardcoded these to January and December month. You can change these based on your financial year.

 

 

 

Start Year = Date(Target[Year],1,1)
Date = date(Target[Year],12,31)

 

 

Screenshot 2020-10-26 09.09.01.png

 

We also added a date table. this table has 2 columns date and year.

 

 

Date = ADDCOLUMNS(CALENDAR(date(2017,01,01),date(2020,12,31)),"Year", year([Date]),"Month Year", FORMAT([Date],"YYYYMM"))

 

 

 

Screenshot 2020-10-26 09.09.14.png

 

We have joined both the tables on date columns.  

 

Screenshot 2020-10-26 09.09.31.png

 

This means Data is only available at the end of the year in the visual.

 

Screenshot 2020-10-26 09.10.08.png

 

Daily Target: To create a daily target we used the closing balance of the year. This will replicate the data for each day of the year. You will be able to see the same number every day. Visual is using date from date table.

 

Screenshot 2020-10-26 09.11.29.png

 

Now we need to divide each of these rows by days of year to get the correct daily target

 

Screenshot 2020-10-26 09.11.58.png

 

For YTD target, We need multiple daily targets by YTD days. That we will get from the date table and TOTALYTD

 

Screenshot 2020-10-26 09.12.20.png

 

We added month year to the date table, but the daily target, in this case, cannot sum up. So, we created a new period target that multiplies it by no of days in a period.

 

Screenshot 2020-10-26 09.31.09.png

 

Let us know what you think about this.


The file can found at https://community.powerbi.com/t5/Quick-Measures-Gallery/Distributing-Allocating-the-Yearly-Target-Co...

 

You can get all my posts at https://community.powerbi.com/t5/Data-Stories-Gallery/Blog-Analysis/m-p/1265567#M4403

 

You can also follow my YouTube channel (YouTube) and LinkedIn (profile) to get information on the upcoming webinars

 

 

Comments