Make monthly goal into daily goal?

[michellerosen]

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? 

[fhill · Accepted solution]

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])))