Budget Per Day for Daily Budget

Rohit1 · November 22, 2021, 9:45am

Division Performance_Upload.pbix (8.3 MB)

When i plot Budgeted Sales against Dates not able to get “Daily Budgeted Sales”
Please assist file attached.

Harsh · November 22, 2021, 1:41pm

Thank You for posting your query onto the Forum.

To achieve the results firstly, you’ll be required to delete the relationship which you’ve created between the “Budget_2021 (2)” table and “Dates” table. And then write the below provided measure as follows -

Budget Allocation = 
VAR DayInContext = 
COUNTROWS( Dates )

VAR DaysInMonth = 
CALCULATE( COUNTROWS( Dates ) , 
    ALL( Dates ) ,
    VALUES( Dates[Month & Year] ) )

RETURN
IF( AND( HASONEVALUE( Dates[Date] ) , HASONEVALUE( Dates[Month & Year] ) ) , 
    DIVIDE( DayInContext , DaysInMonth , 0 ) * [Bud. Sales] , 
    [Bud. Sales] )

Below is the screenshot of the final results provided for the reference -

I’m also attaching the working of the PBIX file as well as video link of the Budget Allocation Methodology for the reference purposes. Also please go through “Budgeting and Forecasting” course which is available onto our education portal.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

Division Performance_Upload - Harsh.pbix (8.3 MB)

Rohit1 · November 22, 2021, 5:34pm

Hi Harsh thanks for the reply.
One challenge the monthly Budgeted Figures are different it is not uniform as per above working.

For Example
Jan Budget 16,913,900.33 Divided by 31 = Per Day AED 545,610
Feb Budget 17,586,066.33 Divided by 28 = Per Day AED 627,967
And so on for
Mar
Apr
…
Dec.

Appreciate if above outcome is possible.

Harsh · November 22, 2021, 7:06pm

Hello @Rohit1,

So to achieve the results based on that condition just added a calculated column in the “Budget_2021 (2)” and then wrote the measure as provided below -

Budget Allocation = 
VAR _Monthly_Budgets = 
CALCULATE( SUM( 'Budget_2021 (2)'[Bud. Sales] ) ,
    TREATAS( VALUES( Dates[Month Name] ) , 'Budget_2021 (2)'[Month Name] ) )

VAR DayInContext = 
COUNTROWS( Dates )

VAR DaysInMonth = 
CALCULATE( COUNTROWS( Dates ) , 
    ALL( Dates ) ,
    VALUES( Dates[Month & Year] ) )

RETURN
IF( AND( HASONEVALUE( Dates[Date] ) , HASONEVALUE( Dates[Month & Year] ) ) , 
    DIVIDE( DayInContext , DaysInMonth , 0 ) * _Monthly_Budgets , 
    _Monthly_Budgets )

Below is the screenshot of the final results provided for the reference -

I’m also attaching the working of the PBIX file for the reference.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

Division Performance_Upload - Harsh v2.pbix (8.2 MB)

BrianJ · November 22, 2021, 8:32pm

@Rohit1 ,

@Harsh’s solution is a good one, and the same approach that I used for a long time. However, I now take a different approach that pushes this allocation closer to the source – doing it either in SQL or Power Query and reducing your DAX to a single, simple line of code.

This is the exact topic of my talk this week at the Enterprise Analytics Summit.

Here’s the link to register if you’re interested:

Brian

Rohit1 · November 24, 2021, 6:19am

Thanks Harsh for the output. When i mapped this in actual file. Management wants to exclude Public Holidays and Week ends. In U.A.E. - Fridays are week end , i have also uploaded Public Holiday Calendar, but not able to figure out

(1) How to eliminate Public Holidays to arrive at Average Daily Budgeted Sales.
(2) If Public Holidays is on Week End how same can be accommodated in point no. 1.

Example for May 2021
5 Public Holidays ( Includes 1 Friday) , 4 Fridays
In total i need to consider 31-8 = 23 Days to arrive Budgeted Daily Average Sale
May Budget 22,678,133.08/23 Days = 986,006 Per Day Budgeted Sale

Uploaded Pbix file along with Holiday Calendar.
Appreciate your valuable feedback.

Division Performance_Upload 22112021.pbix (8.2 MB)

Hemantsingh · November 24, 2021, 1:29pm

Hi @Rohit1 ,

If the problem is only to accommodate the weekends and holidays then usually a calculated column in dates table identifying whether its a workday or not helps. I have created one for you. After that its just dividing the monthly budget numbers with the count of workdays. I have created a calculated column in dates table for your reference too.
I have created an additional measure showing countofworkdays, if you don’t need it you can create this measure as variable within the Average budget PM for WD measure.

Regards,
Hemant

Division Performance_Upload 22112021.pbix (8.2 MB)

Harsh · November 25, 2021, 12:30am

Hello @Rohit1,

Firstly, I replaced the Old M Code of Date Table with the New M Code which is the most comprehensive, compact and contains all the fields which an ideal date table should actually have. And then defined the Weekend as “Friday” based on the condition that you’ve specified and within the date table itself also updated the “Holiday’s” as well.

Once this was done, I just added one more line into my existing measure and achieved the results. Below is the updated measure alongwith the screenshot of the final results provided for the reference -

Budget Allocation 2 = 
VAR _Monthly_Budgets = 
CALCULATE( SUM( 'Budget_2021 (2)'[Bud. Sales] ) ,
    TREATAS( VALUES( Dates[Month Name] ) , 'Budget_2021 (2)'[Month Name] ) )

VAR DayInContext = 
COUNTROWS( Dates )

VAR DaysInMonth = 
CALCULATE( COUNTROWS( Dates ) , 
    Dates[IsBusinessDay] = TRUE() ,
    ALL( Dates[Date] ) , 
    VALUES( Dates[Month & Year] ) )

RETURN
IF( AND( HASONEVALUE( Dates[Date] ) , HASONEVALUE( Dates[Month & Year] ) ) , 
    DIVIDE( DayInContext , DaysInMonth , 0 ) * _Monthly_Budgets , 
    _Monthly_Budgets )

I’m also attaching the working of the PBIX file for the reference purposes.

Thanks and Warm Regards,
Harsh

Division Performance_Upload - Harsh v3.pbix (1.1 MB)

Rohit1 · November 25, 2021, 4:25am

Hi Hemant and Harsh thanks a lot for providing the output.

Hemant im not able to download your file, i get the message to update the version.
My current version 2.92.943.0 64-bit.

Pls. suggest.

Hemantsingh · November 25, 2021, 6:28am

Hi @Rohit1 ,

If you update your pbi desktop to october 2021 or Nov 2021 release it will open.

Regards,
Hemant

Rohit1 · November 25, 2021, 9:52am

Hi @BrianJ excellent session on Granularities through Power Query. Just trying to understand how can i bring in Working Days in a month. Pls. assist if any tutorial on same.

BrianJ · November 25, 2021, 9:15pm

@Rohit1 ,

Thanks – glad you found the session yesterday helpful. If I had a bit more time, I would’ve covered this topic as well but we can do it here, and I may do a supplemental YouTube video since I think it will be a common question.

If you are using the Extended Date Table, all you need to do is the following:

In the step where you bring where you merge the daily dates into the Monthly Budget table, also bring over the IsWorkingDay or the IsBusinessDay field too (they differ depending on whether they include holidays or not, as long as you have a Holiday table linked to your Extended Date Table).

add one more field to the GroupBy step where we count days per month.

Then just unselect IsBusinessDay = FALSE and you will be left with only business days in the Days in Month Column

Now, you can do the daily allocation exactly as described in my presentation, but with nonbusiness days now removed from the Days in Month tallies.

I hope that’s helpful. Please let me know if you have any other questions.

Brian

Rohit1 · November 27, 2021, 6:06am

@Brian , Thanks for above guidance. As i normally i work on Date Calendar without column for Working Days & Business Days , appreciate if there is any guidance on how to generate the column for Working Days/Business Days , my apologies if this is very basic question.

EnterpriseDNA · November 29, 2021, 8:29pm

Bumping this post for more visibility to other users and experts.

BrianJ · November 30, 2021, 1:23am

@Rohit1 ,

Ultimately, you’ll need to make a decision as to whether you want to stick with your DAX-based date table or switch to an M-Code based table. If you stick with the former, you can use the method described by @Hemantsingh above to add a calculated column to your date table to reflect business days. However, I very strongly advise against that route for a number of reasons:

DAX calculated tables are not visible/accessible within PQ - severely limiting what you can do with that date table within your model (for example, the budget allocation process I discuss in the seminar is no longer available to you, while if you use an M-based date table, you still have both the PQ and DAX allocation methods available to you).
@Melissa 's Extended Date table contains a number of time intelligence-based fields (most notably the Offset fields, as well as the workday and business day fields and ISAFTERTODAY) which are extremely powerful and will dramatically simplify many of your TI calculations. Some of the functionality of this table is difficult to replicate in DAX.
The Extended Date Table has been extensively tested and well-documented. You can be confident in using it that it works as advertised and produces accurate results.

Brian

EnterpriseDNA · December 2, 2021, 12:03am

Hello @Rohit1, it’s been a while since we got a response from you.

Did @BrianJ help you solve your question?

If you do, kindly provide the information the experts requested above so they can help you further.

In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

EnterpriseDNA · December 2, 2021, 10:56pm

Hi @Rohit1, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

Kindly take time to answer the Enterprise DNA Forum User Experience Survey, we hope you’ll give your insights on how we can further improve the Support forum. Thanks!