Using a single budget value for every day of the month

[Adams_Apple]

I have a monthly budget table where the data is stored in Date rows with the date being the 1st of each month (1/1/2025, 2/1/2025, etc). I am trying to build out a MTD table that should total running costs next to total running budget, so I can see where the overlap happens every month.

I have the cost MTD dialed in, but i cant seem to figure out how to filter a budget measure so that it ignores the fact that the budget value is only stored on the 1st.
ideally, this measure would take the (month budget / total days in month) * days in the month up to that current date.

help is appreciated!!!

[rajendraongole1 · Accepted solution]

Hi @Adams_Apple  - can you try the below measure:

Labor Amount MTD Budget =
VAR CurrentDate = MAX('YourTable'[Date])
VAR MonthStart = EOMONTH(CurrentDate, -1) + 1
VAR MonthBudget = CALCULATE(
SUM('BudgetTable'[BudgetAmount]),
'BudgetTable'[Date] = MonthStart
)
VAR DaysInMonth = DAY(EOMONTH(CurrentDate, 0))
VAR DaysElapsed = DATEDIFF(MonthStart, CurrentDate, DAY)

RETURN
IF(
MonthBudget > 0,
(MonthBudget / DaysInMonth) * DaysElapsed
)

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

[v-dineshya]

Hi @Adams_Apple ,

Thanks for reaching out to the Microsoft fabric community forum. 

DAX code:

MTD Budget =
VAR CurrentDate = MAX('Date'[Date])
VAR MonthStart = DATE(YEAR(CurrentDate), MONTH(CurrentDate), 1)
VAR DaysInMonth = DAY(EOMONTH(CurrentDate, 0))
VAR DaysUpToCurrent = DAY(CurrentDate)
VAR MonthlyBudget =
CALCULATE(
SUM('Budget'[BudgetAmount]),
FILTER('Date', 'Date'[Date] = MonthStart)
)
RETURN
(MonthlyBudget / DaysInMonth) * DaysUpToCurrent

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thanks and Regards

[v-dineshya]

Hi @Adams_Apple ,

Thanks for reaching out to the Microsoft fabric community forum. 

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thanks and Regards

[v-dineshya]

Hi @Adams_Apple ,

Thanks for reaching out to the Microsoft fabric community forum. 

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thanks and Regards

[v-dineshya]

Hi @Adams_Apple ,

Thanks for reaching out to the Microsoft fabric community forum. 

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thanks and Regards

[danextian · Accepted solution]

Hi @Adams_Apple 

The simplest solution is to split your monthly budget into several rows based on the number of days in a month in the query editor.

If that isn't feasible, you can utilize DAX.

MTD (daily x day of month) = 
VAR _NumberOfDays =
    DAY ( CALCULATE ( MAX ( Dates[Date] ), ALLEXCEPT ( Dates, Dates[YYYYMM] ) ) )
VAR _Daily =
    DIVIDE (
        CALCULATE (
            SUM ( Budget2[Budget] ),
            TREATAS ( VALUES ( Dates[Start of Month] ), Budget2[Month] )
        ),
        _NumberOfDays
    )
RETURN
    _Daily * DAY ( MAX ( Dates[Date] ) )

Please see the attached sample pbix.

[rohit1991]

Hi @Adams_Apple ,
To create a proper MTD budget measure in Power BI when your budget values are stored only on the first day of each month, you’ll need to dynamically distribute that monthly value across each day of the month and calculate a cumulative total based on the current date in your visual. This approach allows you to compare daily running actual costs against a growing budget line that reflects a proportional share of the monthly budget. 

Since your budget table only contains one entry per month (dated the 1st), the DAX measure needs to locate that monthly value, determine how many days are in the current month, and multiply the daily allocation by the current day of the month. This way, the budget grows daily just like your actual labor costs, enabling accurate month-to-date comparisons.

Budget MTD =
VAR CurrentDate = MAX('Calendar'[Date])
VAR StartOfMonth = DATE(YEAR(CurrentDate), MONTH(CurrentDate), 1)
VAR EndOfMonth = EOMONTH(CurrentDate, 0)
VAR DaysInMonth = DAY(EndOfMonth)
VAR DayOfMonth = DAY(CurrentDate)

VAR MonthlyBudget =
    CALCULATE(
        SUM('BudgetTable'[Budget]),
        'BudgetTable'[Date] = StartOfMonth
    )

RETURN
    DIVIDE(MonthlyBudget, DaysInMonth, BLANK()) * DayOfMonth

[rajendraongole1 · Accepted solution]

Hi @Adams_Apple  - can you try the below measure:

Labor Amount MTD Budget =
VAR CurrentDate = MAX('YourTable'[Date])
VAR MonthStart = EOMONTH(CurrentDate, -1) + 1
VAR MonthBudget = CALCULATE(
SUM('BudgetTable'[BudgetAmount]),
'BudgetTable'[Date] = MonthStart
)
VAR DaysInMonth = DAY(EOMONTH(CurrentDate, 0))
VAR DaysElapsed = DATEDIFF(MonthStart, CurrentDate, DAY)

RETURN
IF(
MonthBudget > 0,
(MonthBudget / DaysInMonth) * DaysElapsed
)