cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
michellerosen Frequent Visitor
Frequent Visitor

Make monthly goal into daily goal?

Let's say my month goals are 
July         3000
August    2500

I want to divide 3000 by the number of days in the month and assign each division + the last to each day of the month.

This is what it would look like in excel:

96.77419
193.5484
290.3226
387.0968
483.871

 

(July 1, 2, 3, 4, 5)

Is there any way to do this in powerbi automatically for each month? 

1 ACCEPTED SOLUTION

Accepted Solutions
fhill Established Member
Established Member

Re: Make monthly goal into daily goal?

This link helped:

https://community.powerbi.com/t5/Desktop/Running-Total-DAX/m-p/41429#M15689

 

Table1:  Month (Formatted as Date) and Montly Goal.  Dupliate the Month column, and Transform into 'Days in Month' as shown below.  Make sure MGoal and Days in Month are formatted numbers, and create a 4th custom column dividing the two to get Daily Goal on the Month (Table1) table.

 

Table 2:  Daily Table = One line for each day from a calendar.  (Sometimes I cheat and drag down dates in Excel then copy /past into Power BI.  There's also several posts on how to create a Date Table.)  Create a custom column of 'Month' in Table two with 'STARTOFMONTH' function.  Create a Relationship between the two tables with One to Many of the Month Table.  Now bring over Daily Goal with a Related command below.  (There are other ways to do this also w/o the relationship, but this is the basic way.)  Finally, use the Running Total function to SUM R-DGoal for each (Filter 1) 'change' (EARLIER) in the Date Column AND (Filter 2) 'change' EARLIER in the Month Column.

 

Table2 Functions:

Month = STARTOFMONTH(Table2[Date])

R-DGoal = RELATED(Table1[D Goal])

RunningTotal = CALCULATE(SUM(Table2[R-DGoal]), FILTER(ALLSELECTED((Table2)),Table2[Date] <= EARLIER(Table2[Date])), FILTER(ALLSELECTED(Table2),Table2[Month] >= EARLIER(Table2[Month])))

 

 

Capture.PNGCapture2.PNGCapture3.PNG

1 REPLY 1
fhill Established Member
Established Member

Re: Make monthly goal into daily goal?

This link helped:

https://community.powerbi.com/t5/Desktop/Running-Total-DAX/m-p/41429#M15689

 

Table1:  Month (Formatted as Date) and Montly Goal.  Dupliate the Month column, and Transform into 'Days in Month' as shown below.  Make sure MGoal and Days in Month are formatted numbers, and create a 4th custom column dividing the two to get Daily Goal on the Month (Table1) table.

 

Table 2:  Daily Table = One line for each day from a calendar.  (Sometimes I cheat and drag down dates in Excel then copy /past into Power BI.  There's also several posts on how to create a Date Table.)  Create a custom column of 'Month' in Table two with 'STARTOFMONTH' function.  Create a Relationship between the two tables with One to Many of the Month Table.  Now bring over Daily Goal with a Related command below.  (There are other ways to do this also w/o the relationship, but this is the basic way.)  Finally, use the Running Total function to SUM R-DGoal for each (Filter 1) 'change' (EARLIER) in the Date Column AND (Filter 2) 'change' EARLIER in the Month Column.

 

Table2 Functions:

Month = STARTOFMONTH(Table2[Date])

R-DGoal = RELATED(Table1[D Goal])

RunningTotal = CALCULATE(SUM(Table2[R-DGoal]), FILTER(ALLSELECTED((Table2)),Table2[Date] <= EARLIER(Table2[Date])), FILTER(ALLSELECTED(Table2),Table2[Month] >= EARLIER(Table2[Month])))

 

 

Capture.PNGCapture2.PNGCapture3.PNG