cancel
Showing results for 
Search instead for 
Did you mean: 

Distributing/Allocating the Yearly Target (Convert to Daily Target) -Part 1 - New Table

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.

The target table has 2 columns: Target and Year.

 

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 year columns.  This will make the target on one side of the Many to one Relation.

 

Screenshot 2020-10-26 09.43.22.png

 

We created a new table using the summarize function. In that we created a new column, That divides the target by the number of days in the year. We already have taken the start date and end date in our table and we have used these two to find date difference in days. This has given us a daily target Table.

 

 

 

Daily Target = SUMMARIZE('Date', 'Date'[Date], Target[Year], "Daily Target" , CALCULATE(sum(Target[Target])/(DATEDIFF(min(Target[Start Year]),max(Target[Date]),day)+1)))

 

 

 

Screenshot 2020-10-26 09.45.28.png

 

Joined it back with the date table on the date, so that we can use it in the visual with the date table.

 

Screenshot 2020-10-26 09.43.43.png

 

Daily Target

 

Screenshot 2020-10-26 09.44.24.png

 

For the YTD target We have used TOTALYTD

 

 

YTD Target = TOTALYTD(sum('Daily Target'[Daily Target]),'Date'[Date]) 

 

 

 

Screenshot 2020-10-26 09.44.44.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.