Skip to main content
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now


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


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.



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



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






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


You can get all my posts at


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