Budget Allocation for weekday only

[KiwiNigel]

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

[BrianJ]

@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

[KiwiNigel]

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

[BrianJ]

@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

[KiwiNigel]

@BrianJ

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

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

[BrianJ]

@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

[KiwiNigel]

@KiwiNigel

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

@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

[BrianJ]

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

How To Mask Sensitive Data In Power BI

• Brian

[KiwiNigel]

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

[BrianJ]

@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

[KiwiNigel]

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

[BrianJ]

@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

[Melissa]

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

[KiwiNigel]

@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() )

[EnterpriseDNA]

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!

https://analysthub.enterprisedna.co/dax-clean-up