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

Power BI Distributing/Allocating the Monthly Target(Convert to Daily Target): Measure ( Daily/MTD)

Objective:

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

 

Data:

The target table has 4 columns: Target and Month. Quarter and Year

 

Solution:

We have added 2 new columns to the target table Month start date and the Month end date (Named it Date).

 

Screenshot 2021-02-09 13.29.09.png

 

 

Month Date = "01-" &[Order_Month] & "-" &[Years] // Month Start Date. Changed Data Type to Date
Date = EOMONTH([Month Date],0) // Month End Date

 

 

Screenshot 2021-02-10 18.37.47.png

 

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

 

 

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

 

 

Screenshot 2021-02-10 18.37.53.png

 

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

 

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

 

Screenshot 2021-02-10 18.59.59.png

 

 

Measure 

 

 

Daily Target = CLOSINGBALANCEMONTH(sum(Target[Month Target])/(maxx(Target,DATEDIFF(Target[Month Date],Target[Date],day))+1),'Date'[Date])

MTD Target = CLOSINGBALANCEMONTH(sum(Target[Month Target])/(maxx(Target,DATEDIFF(Target[Month Date],Target[Date],day))+1),'Date'[Date]) *TOTALMTD(COUNT('Date'[Date]),'Date'[Date])

 

 

Screenshot 2021-02-10 18.49.05.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-Monthly-Target-C...

 

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

Thanks, it's very near to my goal, and now I want to find how many days are spend in this month till date (.i.e. 17 Days) and similarly how many days are spent in this year(.i.e. 76 days).

 

 

Dear Sir, thank you very much for immediate response but i think i can't explain my problem properly.... now i try again to explain my problem:

  •  I have a tabel in which there are day wise and product wise target ratio %s... like

Refrigerator Day wise Target (plan) Ratios %

Deep Freezer Day wise Target (plan) Ratios %

Split Ac Day wise Target (plan) Ratios %

(Marco wave oven, Water Dispenser and some other porducts).

  • i have another table in which there are customer/ Dealer wise, product wise and SKU wise (model wise and color wise and size wise) data (targets /plan). In this data there is no proper date and we can’t insert proper date here if we insert date here then we need 10,000,000 rows on excel! 

My Problem is:

Dear Sir i want to split my month target on day level but with target (plan) ratios % table beacuse these ratios % will be change every month.

Please guide me which measures, method and pattern suitable for to split target (plan) with availed ratios %s table.

Thank you Very Much.

 

 

 

Day wise Target Ratio %.pngtarget table.png

relationship.png

 

Polls
What is your favorite Power BI feature release for June 2022?