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?
Solved! Go to Solution.
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
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.
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?
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.