Calculate daily budget from monthly budget

[nigel_s]

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?

[nigel_s · Accepted solution]

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

[Anonymous]

Hi Nigel,

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

Regards,

//Peter

[nigel_s]

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

[AlB]

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 

[nigel_s]

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?