 # Budget Allocation for weekday only

Hi All,
I am using the DAX below to allocate a monthly budget to each day, but I only want to allocate on weekdays as we do not routinely sell on a weekend.
So I need to be able to work out what the daily value is based on the number of weekdays in the month
Tehn only allocate this value to weekdays

Budget Allocation =
VAR MonthDays = CALCULATE( COUNTROWS( Dates ), ALL( Dates ), VALUES( Dates[FY Month & Yr] ))
VAR DailyBudget = [Total Budget (FX)]

RETURN
if( ISFILTERED( Dates[Date] ),
DIVIDE( DailyBudget, MonthDays, 0 ),
[Total Budget (FX)] )

Here’s one way to do it:

``````Weekdays Per Month =

CALCULATE(
COUNTROWS( Dates ),
ALL( Dates ),
VALUES( Dates[Month & Year] ),
LEFT( Dates[DayOfWeekName], 1) <> "S"
)
``````

Lots of different ways to handle this depending on what fields you have available in your date table. In Power Query, you could create a conditional field called IsWeekday, and then filter on that in place of the LEFT(1) construct above.

• Brian

Hi Brian,

I have a column for Weekday (Weekday & Weekend) already in the Dates table
Just need to understand how to utilise it in the DAX calculation

Nigel

OK, in that case this should work as your MonthDays variable:

``````CALCULATE(
COUNTROWS( Dates ),
ALL( Dates ),
VALUES( Dates[FY Month & Year] ),
Dates[Weekday] = TRUE
)
``````

It would be helpful if you could post your PBIX so that I can see whether your [Total Budget (FX)] measure also has to be adjusted.

• Brian

That has worked but still allocating \$\$ to weekends

Budget Allocation =
VAR MonthDays = CALCULATE( COUNTROWS( Dates ), ALL( Dates ), VALUES( Dates[FY Month & Yr] ), Dates[Weekday] = “Weekday”)
VAR DailyBudget = [Total Budget (FX)]

RETURN
if( ISFILTERED( Dates[Date] ),
DIVIDE( DailyBudget, MonthDays, 0 ),
[Total Budget (FX)] ) Okay, that means your daily budget calculation also needs to be adjusted to filter out weekend days.

If you can please post your PBIX file I should be able to get back to you quickly with a full solution.

Thanks.

• Brian

@BrianJ, I unfortunately can’t as it contains confidential info.
This calculation is literally all I use to allocate the monthly budget to each day

Nigel

The problem is your DailyBudget variable is calling this measure: [Total Budget (FX)]. Without knowing what that measure’s doing I can’t make any additional progress on providing you a solution. At minimum, can you please send me the DAX code for that measure?

Also FYI, here’s a video I put together on quick techniques for anonymizing confidential data. Let’s see if we can solve this with just the measure code first though.

Thanks.

• Brian

Hi @BrianJ
Total Budget is literally just the sum of the Budget value column but I add a variable to the Total Budget (FX) so it automatically changes for AUD or USD

Total Budget = calculate(
sum(‘Budget 2020’[Sales Budget USD]),
TREATAS( VALUES( Dates[FY Month & Yr] ) , ‘Budget 2020’[Month & Year] ))

Total Budget (FX) = SWITCH( TRUE(),
[Selected Currency] = “AUD”, [Total Budget] * VALUES( ‘FX Rate’[FX Rate] ),
[Total Budget])

Nigel

Now try modifying your Total Budget measure to filter out amounts assoc. w/ weekend days:

``````Total Budget =

VAR TotWeekday =
CALCULATE(
SUM(‘Budget 2020’[Sales Budget USD]),
Dates[Weekday] = "Weekday"
)

VAR Result =
CALCULATE(
TotWeekday,
TREATAS(VALUES( Dates[FY Month & Yr] ) , ‘Budget 2020’[Month & Year] )
)

RETURN
Result
``````
• Brian

The Budget is a monthly number per territory per product group.
It is not set as a daily number

That is why I am doing the Allocation calculation dividing it by the number of weekdays (Which is working)
But now I just need to allocate this daily value back to each weekday, and ignore the weekends.
Hope that makes sense

Nigel

To provide a specific, working solution on this I’m going to need an anonymized/masked PBIX file. Otherwise, without being able to see the whole picture including your data model, I’m just taking shots in the dark.

Thanks.

• Brian

If it’s just about displaying the value you can add another IF clause something like below.
Else please provide a sample PBIX file as @BrianJ mentioned.

``````Budget Allocation =
VAR MonthDays =
CALCULATE(
COUNTROWS( Dates ),
ALL( Dates ),
VALUES( Dates[FY MONTH & Yr] ),
Dates[Weekday] = "Weekday"
)
VAR DailyBudget = [Total Budget( FX )]

RETURN
IF(
ISFILTERED( Dates[Date] ),
IF( Dates[Weekday] = "Weekday",
DIVIDE( DailyBudget, MonthDays, 0 ),
BLANK()
),
[Total Budget( FX )]
)``````

@Melissa, the if option did not work but I got things working when I used And with a contains function

Budget Allocation =
VAR MonthDays = CALCULATE( COUNTROWS( Dates ), ALL( Dates ), VALUES( Dates[FY Month & Yr] ),Dates[Weekday] = “Weekday”)
VAR DailyBudget = [Total Budget (FX)]

RETURN
if(and(ISFILTERED( Dates[Date] ),CONTAINS( Dates,Dates[Weekday],“Weekday”)),
DIVIDE( DailyBudget, MonthDays, 0 ),
blank() )

Please be sure to check out our DAX Clean Up tool it’s a great way to ensure to make your DAX code easy to read. Thanks!