Allocating budgets on a different level is not easy to manage in DAX. It was (and still is) a DAX struggle for me.
This blog post will guide you through the different steps I took to easily manage the different granularities (and write easy DAX afterwards) by allocating budget on day level using Power Query.
I created several sales reports, but in order to get a complete view and gain more insights in the figures, I had to include the budget figures.
The customer provided excel budget files, but these files were created using different aggregation levels. The source files were weekly budgets for department A and monthly budgets for department B. Reporting on the other side required weekly and monthly analysis for both departments.
As you might have noticed, a week can contain dates of multiple months, e.g. Sunday 29 September to Saturday 5 October contains 2 days of September and 5 days from October.
When we look at a month on the other side, we can have problems on both sides of the month. The first week of the month can contain dates from the previous month and the last week of the month can contain dates from the next month.
As we can conclude, budget allocation handling different granularities is inevitable.
As I like to play with DAX, I started writing different formulas, but never achieved the correct result. The only solution I knew would work was to change my dataset using Power Query. The below steps will provide you an easy way to handle this difference in granularities.
Step overview
Let’s dive into the details
Let’s start by loading the budget data into you Power BI model.
Make sure you have at least the following columns:
Example: a monthly budget file
Generate a calendar table.
Make sure this table also contains the ‘YYYYMM’ and ‘YYYYWW’ columns.
We only allocated budget to working days, so we added an additional column: ‘IsWorkingDay’ (1= working day, 0 = non working day). You might need to create your own logic for this depending on holidays, weekends,…
= if [IsWeekend] = false and [IsHoliday] = false then 1 else 0 |
Using the calendar table generated before, we now create an aggregated table to determine the number of working days by month or by week (depending on the use case). We needed both so generated 2 additional tables, one by month and another one by week.
To achieve this, right click the calendar table and choose ‘reference’.
Next use the ‘group by’ functionality and group by YYYYMM or YYYYWW and as aggregation add: SUM(IsWorkingday) as NumberOfWorkingDaysInMonth/Week
The YYYYMM or YYYYWW column is required to link the budget table with the aggregated calendar table.
This will generate a 2 columns table, one indicating the month or week, and another the number of working days in that period.
To determine the budget per day, we merge the budget table with the aggregated calendar table.
To add columns for the merged table, simply click the expand icon and select the column to add.
After this merge we now have the following columns
To get the DayValue we simply add a custom column.
After calculating the DayValue, you can remove the Value column (or rename it to “MonthValue” or “WeekValue”), so the distinction is clear for the report designer.
As a final step in preparing our data, we merge the budget table with the calendar table (the one on day level). This allows generating one line for each calendar day.
We merge this on the YYYYMM or YYYYWW column.
When expanding the calendar table, only adding the Date or DateKey (YYYYMMDD) column is enough.
As you don’t need the aggregated Calendar by month table, you can disable load for this table.
Now your budget table is available on day level and easy to create the required DAX measures to generate the correct calculation.
Notice when writing DAX formulas you only sum the DayValues on working days.
= CALCULATE(SUM(Budget[DayValue]); Calendar[IsWorkingDay] = 1) |
As a workaround you can create a calculated column so a DayValue is only available on working days.
= IF(RELATED(Calendar[IsWorkingday]) = 1; Budget[DayValue]) |
Cookie | Duration | Description |
---|---|---|
ARRAffinity | session | ARRAffinity cookie is set by Azure app service, and allows the service to choose the right instance established by a user to deliver subsequent requests made by that user. |
ARRAffinitySameSite | session | This cookie is set by Windows Azure cloud, and is used for load balancing to make sure the visitor page requests are routed to the same server in any browsing session. |
cookielawinfo-checkbox-advertisement | 1 year | Set by the GDPR Cookie Consent plugin, this cookie records the user consent for the cookies in the "Advertisement" category. |
cookielawinfo-checkbox-analytics | 11 months | This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics". |
cookielawinfo-checkbox-functional | 11 months | The cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional". |
cookielawinfo-checkbox-necessary | 11 months | This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary". |
cookielawinfo-checkbox-others | 11 months | This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other. |
cookielawinfo-checkbox-performance | 11 months | This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance". |
CookieLawInfoConsent | 1 year | CookieYes sets this cookie to record the default button state of the corresponding category and the status of CCPA. It works only in coordination with the primary cookie. |
elementor | never | The website's WordPress theme uses this cookie. It allows the website owner to implement or change the website's content in real-time. |
viewed_cookie_policy | 11 months | The cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data. |
Cookie | Duration | Description |
---|---|---|
__cf_bm | 30 minutes | Cloudflare set the cookie to support Cloudflare Bot Management. |
pll_language | 1 year | Polylang sets this cookie to remember the language the user selects when returning to the website and get the language information when unavailable in another way. |
Cookie | Duration | Description |
---|---|---|
_ga | 1 year 1 month 4 days | Google Analytics sets this cookie to calculate visitor, session and campaign data and track site usage for the site's analytics report. The cookie stores information anonymously and assigns a randomly generated number to recognise unique visitors. |
_ga_* | 1 year 1 month 4 days | Google Analytics sets this cookie to store and count page views. |
_gat_gtag_UA_* | 1 minute | Google Analytics sets this cookie to store a unique user ID. |
_gid | 1 day | Google Analytics sets this cookie to store information on how visitors use a website while also creating an analytics report of the website's performance. Some of the collected data includes the number of visitors, their source, and the pages they visit anonymously. |
ai_session | 30 minutes | This is a unique anonymous session identifier cookie set by Microsoft Application Insights software to gather statistical usage and telemetry data for apps built on the Azure cloud platform. |
CONSENT | 2 years | YouTube sets this cookie via embedded YouTube videos and registers anonymous statistical data. |
vuid | 1 year 1 month 4 days | Vimeo installs this cookie to collect tracking information by setting a unique ID to embed videos on the website. |
Cookie | Duration | Description |
---|---|---|
ai_user | 1 year | Microsoft Azure sets this cookie as a unique user identifier cookie, enabling counting of the number of users accessing the application over time. |
VISITOR_INFO1_LIVE | 5 months 27 days | YouTube sets this cookie to measure bandwidth, determining whether the user gets the new or old player interface. |
YSC | session | Youtube sets this cookie to track the views of embedded videos on Youtube pages. |
yt-remote-connected-devices | never | YouTube sets this cookie to store the user's video preferences using embedded YouTube videos. |
yt-remote-device-id | never | YouTube sets this cookie to store the user's video preferences using embedded YouTube videos. |
yt.innertube::nextId | never | YouTube sets this cookie to register a unique ID to store data on what videos from YouTube the user has seen. |
yt.innertube::requests | never | YouTube sets this cookie to register a unique ID to store data on what videos from YouTube the user has seen. |
Cookie | Duration | Description |
---|---|---|
WFESessionId | session | No description available. |