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

@KiwiNigel,

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.

Hope this is helpful.

  • 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

@KiwiNigel,

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

image

@KiwiNigel,

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

@KiwiNigel,

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

@KiwiNigel,

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

@KiwiNigel,

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

@KiwiNigel,

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!