topic Re: Calculate daily budget from monthly budget in DAX Commands and Tips

Calculate daily budget from monthly budget

Anonymous — Wed, 27 May 2020 06:03:00 GMT

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?

Re: Calculate daily budget from monthly budget

AlB — Wed, 27 May 2020 07:34:04 GMT

Hi Anonymous

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

Re: Calculate daily budget from monthly budget

Anonymous — Wed, 27 May 2020 23:30:28 GMT

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?

Re: Calculate daily budget from monthly budget

Anonymous — Tue, 02 Jun 2020 01:20:54 GMT

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

Re: Calculate daily budget from monthly budget

Anonymous — Thu, 11 Jun 2020 08:49:37 GMT

Hi Nigel,

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

Regards,

//Peter

Re: Calculate daily budget from monthly budget

Anonymous — Fri, 12 Jun 2020 00:27:06 GMT

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