Distribute Monthly Target Values Across Daily Accrual Records in Power BI
Question details
Allocate each monthly target across daily accrual records for the matching point ID and month.
- Product
- Microsoft Power BI data model and DAX
- Device & OS
- not provided
- Scenario
- A report contains monthly target values, daily accrual records, and a calendar table that must be used to calculate a daily target allocation.
- Observed behavior
- The monthly target must be divided by the applicable number of days and aggregated correctly for each point ID and date.
Confirm that the Calendar table includes every reporting date and is marked as a date table if required by your model. Also confirm whether targets should be spread across calendar days, business days, or only dates with recorded accruals.
Add a DaysInMonth column to the Calendar table
Use this method when monthly targets should be allocated evenly across every calendar day in the month.
A Calendar table provides a consistent date dimension for daily accrual records and monthly target calculations. The DaysInMonth value should return the total number of calendar days for the month containing each date.
In Power BI Desktop, select the Calendar table in Data view or Model view, then choose Table tools > New column.
Enter the DAX formula: DaysInMonth = DAY(EOMONTH('Calendar'[Date], 0)). This returns 28 to 31 based on each row's month.
Choose Table tools > New column and create a common month key, such as MonthStart = DATE(YEAR('Calendar'[Date]), MONTH('Calendar'[Date]), 1), for matching monthly target rows.
Sort the Calendar table by Date and verify that every date in the same month displays the same DaysInMonth value.
Relate daily accruals, calendar dates, and monthly targets
Use this method when targets and accruals are stored in separate tables and must be matched by point ID and reporting month.
Calculate the daily target amount with a DAX measure
Use a measure when the report needs the daily allocation to respond to filters, visuals, and aggregations.
Calculate Daily Allocations from Monthly Targets in WPS Spreadsheet
WPS Spreadsheet can distribute monthly target values across daily records with a calendar sheet, a days-in-month formula, and lookup formulas that match each point ID and month. This is useful when you need a transparent worksheet-based allocation instead of a DAX data model.
- 1. Create a calendar column: In WPS Spreadsheet, enter each reporting date in a Calendar sheet and add a MonthStart column with =DATE(YEAR(A2),MONTH(A2),1).
- 2. Calculate days in the month: In the next Calendar column, enter =DAY(EOMONTH(A2,0)) and fill the formula down for all calendar dates.
- 3. Add a matching month to accruals: In the Accruals sheet, add a MonthStart column using =DATE(YEAR([@Date]),MONTH([@Date]),1), or use equivalent cell references.
- 4. Retrieve the monthly target: Use SUMIFS to match the target by point ID and month, for example =SUMIFS(Targets!$C:$C,Targets!$A:$A,[@PointID],Targets!$B:$B,[@MonthStart]).
- 5. Calculate the daily target: Divide the retrieved monthly target by the DaysInMonth value returned from the Calendar sheet, then fill the formula down the daily accrual table.
Frequently Asked Questions
How do I calculate the number of days in a month in DAX?
Create a calculated column in the Calendar table with DaysInMonth = DAY(EOMONTH('Calendar'[Date], 0)). The formula returns the last day number of the month, which is the total number of days.
Should a monthly target be divided by calendar days or recorded accrual days?
Use calendar days when the target applies continuously throughout the month. Use recorded accrual days only when the business rule allocates targets exclusively to dates that have qualifying records. Business-day allocation requires a Calendar table flag such as IsBusinessDay.
Why does my daily target not total to the monthly target?
Check that the visual includes all dates in the month, the target is matched to the correct point ID and month, and the DaysInMonth value comes from the same Calendar date context. Missing dates and incorrect relationship filters commonly cause differences.
Can I use Power Query instead of DAX to add DaysInMonth?
Yes. In Power Query, select Add Column > Custom Column and use Date.DaysInMonth([Date]). Load the resulting Calendar table back into the model and use that column in your allocation calculation.
How can I allocate targets only across business days?
Add an IsBusinessDay column to the Calendar table, mark weekdays and exclude holidays, then calculate the count of rows where IsBusinessDay is true for the current month. Divide the monthly target by that business-day count rather than by DaysInMonth.
