Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

Calculate daily budget from monthly budget


I have a budget that is monthly and I want to convert that into daily values, simple in SQL but I'm new to DAX.

The ContractBudget table is imported from SQL












The DateDax is a date table generated by DAX. I currently have no relationship between the tables. In SQL I'd join ContractBudget.Date=DateDax.FirstDayOfMonth to get a budget value for every day of the month but PowerBI says that's a many to many, and there be grumpy dragons ahead.

How do I get a budget value for each day where I divide DecimalBudget by Days In Month to arrive at my daily value?

I'd do a merge in Power Query but calculated tables don't show up there. Is that correct?















Frequent Visitor

I did the join in SQL, much easier than messing around with this in DAX.

View solution in original post

Frequent Visitor

I did the join in SQL, much easier than messing around with this in DAX.

Not applicable

Hi Nigel,

I have basically the same problem as you, and DAX syntax seems very complicated. Can you share your SQL solution?




Hi Peter,


The simplest way is to have a Date table in SQL, just like you'd generate with DAX. You can cover 100 years with ~30K records. This table has at minimum the following columns Date, FirstDayOfMonth, DaysInMonth. You need a budget table with entries for the first of each month. 

Date        FirstOfMonth  DaysInMonth
01/01/2020  01/01/2020    31
02/01/2020  01/01/2020    31
03/01/2020  01/01/2020    31
01/02/2020  01/02/2020    28
02/02/2020  01/02/2020    28

Join Contract.Month to Date.FirstOfMonth and you'll get an entry for each day in the calendar.

SELECT DateKey.Date, ContractBudget.ContractID, SUM(ContractBudget.DecimalBudget) / DateKey.DaysInMonth AS BudgetAmount
FROM ContractBudget 
INNER JOIN DateKey ON DateKey.FirstDayOfMonth = ContractBudget.Month 
GROUP BY DateKey.Date, DateKey.DaysInMonth, ContractBudget.ContractID
Super User
Super User

Hi @nigel_s 

Since the Date column in your table seems to always have the first day of the month, you can create a calcualted column:

Calc column =
VAR daysInMonth_ =
        DateTable[Days In Month],
        DateTable[First Day Of Month], Table1[Date]
    DIVIDE ( Table1[DecimalBudget], daysInMonth_ )


Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.



Frequent Visitor

That gave me the daily budget for each month but what about if I want to build a visual based on days? Where I need a ContractBudget row for each day in the month? Basically a join on ContractBudget[Date]=DateDax[First Day of Month].


Essentially it would be a CROSSJOIN with a Filter but that's terribly inefficient. How do I make use of the common field?


Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors