cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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

ContractBudget.png

 

 

 

 

 

 

 

 

 

 

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?

DateDax.png

 

 

 

 

 

 

 

 

 

 

 

 

 

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.

View solution in original post

5 REPLIES 5
Highlighted
Super User III
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 

SU18_powerbi_badge

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.

View solution in original post

Highlighted
Helper II
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

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

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

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Upcoming Events

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

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.