Calculate Full Month Budget By Number Of Working Days Per Month

[rbowen]

I'm trying to determine how to calculate the amount of budget dollars per day based on the number of work days in a given month. I need to compare sales to budget on a daily, weekly and month to date basis so we can see how our sales are performing relative to budget. The table below shows the budget for Product A for Location 1 for fiscal year 2025. It includes the fiscal month start and ending dates for each month of the fiscal year. As you can see, the budget dollars are expressed in terms of the full month and not broken down by the number of working days per month. This is what I need to calculate.

Using this month as an example, there are 20 US working days for the month of February and the total target sales budget for the month is $4,610.00. So, each working day of this month, Location 1 needs to sell at least $230.50 of Product A to be on target with the projected budget for each working day (Monday thru Friday). Whatever DAX is used, it must also take into account the major US holidays automatically.

I have a date table containing all calendar dates for our fiscal year - September 1st thru August 31st. I have an active, one to many relationship between Dates[CalendarDate] and Budget[Period Start Date]. I've been doing some investigation as to what expression(s) to use but am not certain how to approach this. Any guidance would be greatly appreciated.  Thank you.

[rbowen]

After a bit more investigation, I determined it would help if I created a calendar that included the US holidays as one of the columns. This, I think, gets me a bit closer to the goal. Also, in my budget table, I've created a calculated column that calculates the working days between the period start and end dates. As a test, I've also created a calculated column that finds the budget dollars per day given the number of work days. 

Here's the budget table example with February highlighted for reference. The $1,383.00 amount is the total budget value 6 days into February ($230.50 * 6). This is what I need to calculate for each product and location based on either a date slider or slicer containing a list of the fiscal months. Any ideas?

Also for reference, here is my new Calendar table:

[v-zhouwen-msft · Accepted solution]

Hi @rbowen ,

If I'm misunderstanding, please provide the .pbix file without sensitive data.

If you are unsure how to upload data please refer to

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

The Table data is shown below:

I don't find this relationship useful for calculations, so I didn't create a table-to-table relationship .

I have an active, one to many relationship between Dates[CalendarDate] and Budget[Period Start Date].

 Use the following DAX expression to create a measure

MEASURE =
VAR _table =
    ADDCOLUMNS (
        'Budget',
        "perDayBudget", DIVIDE ( [BudgetAmount], [WorkDays] ),
        "YearMonth",
            YEAR ( [Period Start Date] ) * 100
                + MONTH ( [Period Start Date] )
    )
VAR _table2 =
    ADDCOLUMNS (
        SUMMARIZE (
            FILTER ( 'Calendar', [WeekDay] <= 5 && ISBLANK ( [Holiday] ) ),
            [YearMonth],
            "perMonthWorkDay", COUNTX ( 'Calendar', [Date] ),
            "Date", STARTOFMONTH ( 'Calendar'[Date] )
        ),
        "Result",
            [perMonthWorkDay]
                * MAXX (
                    FILTER ( _table, [YearMonth] = EARLIER ( [YearMonth] ) ),
                    [perDayBudget]
                )
    )
RETURN
    SUMX ( _table2, [Result] )

Final output  (Depending on the slicer selection, there are 6 working days in September and 4 working days in October...' 2492.78 = (5260 / 20 * 6) + (5260 / 23 * 4)')

Best Regards

[rbowen]

Hi v-zhouwen, thanks for the reply. Using your DAX, I get a value that's somewhat close, but still not fully correct. Using Product A and Location 1 for February as an example, I get a value of $1,152.50. As we are now 7 days into February, the value should be 1,613.50 (230.50 * 7). I've created a sample pbix file, the link is below.

SamplePBIX 

[v-zhouwen-msft]

Hi @rbowen ,

As we are now 7 days into February

Meaning there's no need to determine which dates are working days and which are not?

Measure = 
VAR _fiscalMonth = SELECTEDVALUE(Budget[FiscalMonth])
VAR _day = COUNTROWS(FILTER(VALUES('Calendar'),[FiscalMonth] = _fiscalMonth))
RETURN _day * SELECTEDVALUE(Budget[BudgPerDay])

Best Regards

[rbowen]

I may have spoken too soon. While I think your first DAX suggestion is the way to go, it seems to be including weekends in the calculation. In the line below:

 FILTER ( 'Calendar', [WeekDay] <= 5 && ISBLANK ( [Holiday] ) ),

 I changed <=5 to be 

FILTER ( 'Calendar', [WeekDay] IN {1, 2, 3, 4, 5} && ISBLANK ( [Holidays] ) ) ,

My thought was that the less than sign may be including days we don't want. However, I'm still not getting the correct month to date budget value. From my original example above, we are now 6 working days into February (the 3rd - 7th, and today). So today's budget value should be $1,383.00. However, the value with the current DAX formula is $1,613.50 - it's calculating the value for 7 days instead of 6. Is there something else I should be be looking at?

[rbowen]

There is still a need to make the calculation based on working days versus non-working days. Holidays must also be factored in as well. However, as I've been thinking about this, I may have been approaching it incorrectly. As of the 7th of last week, we were only 5 working days into the month so I'm thinking your initial DAX caculation was indeed the way to go. I'm going to do further investigation on this and build out a new report. Thank you so much for your help on this!  😊