cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Monthly budget shown by day

Hi,

I am stuck trying to figure out how to write a measure to show my budget broken down by day. It is currently aggragated at the month level. I would like to divide the budget by the days in the month and plot it on the chart. Each day of the month should have the same value.

I have updated a sample file and data in the link. I appreciate any help, thanks!

``````Budget Chart =
VAR MonthYear = SELECTEDVALUE(Dates[Year Month])
VAR BudgetByMonth =
CALCULATE(
SUM(Budget[Budget Value]),
Dates[Year Month] = MonthYear
)
VAR DaysPerMonth =
CALCULATE(
COUNT(Dates[Date]),
REMOVEFILTERS(Dates[Date]),
Dates[Year Month] = MonthYear
)
VAR BudgetPerDay =
DIVIDE(BudgetByMonth, DaysPerMonth, "Error")

RETURN
BudgetPerDay``````

2 REPLIES 2
Solution Sage

Instead of thinking about partitioning your budget in DAX (which is not only error-prone but also makes DAX slow), create a table that will keep your budget partitioned by day (a budget fact table where the granularity now will be "day", not "month" or something similar). Once you have this table, everything becomes dead easy because you can "regain" your old budgets simply by summing the allocated pieces over a suitable period of time and, what's more important, this will work on ANY PIECE OF TIME. Is this not easier and faster?

Your budget formula now becomes... simply SUM( Budget[Amount] ).

Super User

Hi @patrail

You're certainly thinking along the right lines. I've tweaked things a bit and attached updated PBIX.

Here's how I would write it:

• First add a Days In Month column to your Dates table, containing the total number of days in the month for each Date.
• Then create this Budget Allocated measure:
``````Budget Allocated =
SUMX (
-- Summarize Dates by Year/Month & Days in Month
SUMMARIZE (
Dates,
Dates[Year Month],
Dates[Days In Month]
),
-- For each Year/Month get # days in current filter context for that Year/Month
VAR DayCount =
CALCULATE ( COUNTROWS ( Dates ) )
-- Budget value for the entire month
VAR BudgetValueMonth =
CALCULATE (
SUM ( Budget[Budget Value] ),
ALLEXCEPT ( Dates, Dates[Year Month] )
)
-- Budget value allocated to required number of days (DayCount)
VAR BudgetValueAllocated =
BudgetValueMonth * DayCount / Dates[Days In Month]
RETURN
BudgetValueAllocated
)``````

This measure will allocate the Budget Value correctly over any arbitrary date selection.

The measure iterates over each Year Month that is visible in the current filter context (corresponding to the SUMMARIZE(...) table), and weights the Budget Value by the number of days in the filter context within that month (DayCount / Dates[Days In Month]).

You end up with a step-function when plotting Budget Allocated by Date, which was hopefully what you were looking for.

Is that what you were looking for?

Regards,

Owen

Owen Auger
Blog