cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Calculate daily budget from monthly budget

Hello,

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Frequent Visitor

## Re: Calculate daily budget from monthly budget

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

5 REPLIES 5
Highlighted
Super User III

## Re: Calculate daily budget from monthly budget

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_ =
LOOKUPVALUE (
DateTable[Days In Month],
DateTable[First Day Of Month], Table1[Date]
)
RETURN
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.

Cheers

Highlighted
Frequent Visitor

## Re: Calculate daily budget from monthly budget

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?

Highlighted
Frequent Visitor

## Re: Calculate daily budget from monthly budget

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

Highlighted
Helper II

## Re: Calculate daily budget from monthly budget

Hi Nigel,

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

Regards,

//Peter

Highlighted
Frequent Visitor

## Re: Calculate daily budget from monthly budget

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``````

Announcements

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### July 2020 Community Highlights

Learn about the exciting things that happened in July.

#### Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

#### Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors