cancel
Showing results for
Search instead for
Did you mean:
Highlighted
michellerosen 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.7742 193.548 290.323 387.097 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

## 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])))   1 REPLY 1
fhill 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])))   