# Budget Per Day for Daily Budget

When i plot Budgeted Sales against Dates not able to get “Daily Budgeted Sales”

Hello @Rohit1,

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

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.

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

1 Like

@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

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.

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

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 ) ,
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

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

My current version 2.92.943.0 64-bit.

Pls. suggest.

Hi @Rohit1 ,

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

Regards,
Hemant

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.

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:

1. 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).

1. 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

@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.

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

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:

1. 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).

2. @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.

3. 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

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