Skip to main content
cancel
Showing results for 
Search instead 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

Reply
patrail
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
daXtreme
Solution Sage
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] ).

OwenAuger
Super User
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.

OwenAuger_0-1649496131988.png

Is that what you were looking for?

Regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors