Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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])))
Proud to give back to the community!
Thank You!
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])))
Proud to give back to the community!
Thank You!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |