Monthly budget shown by day

[patrail]

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

[daXtreme]

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

[OwenAuger]

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