Monthly budget allocation to daily/weekly excluding weekends and holidays

[jajecko]

Hi, 
I'm trying figure out how to handle Sales Budget allocation to weekly/daily level. I'm trying to use one of the tutorials found online.

The thing is that i'd like to split the budget only on working days, exluding weekends and holidays. Of course I have it covered in my calendar with column [isWorkingDay]=0/1

BudgetSplit = 
VAR FirstMdate =
    STARTOFMONTH(calendar[Date])
VAR LastMdate =
    ENDOFMONTH(calendar[Date])
VAR BudgetDates =
   DATESBETWEEN (calendar[Date], FirstMdate, LastMdate )
VAR BudgetToDivide =
    CALCULATE ( [Total target], calendar[Date] IN BudgetDates )
VAR NumOfDays =
    COUNTROWS (BudgetDates)
VAR DailyAmount =
    DIVIDE ( BudgetToDivide, NumOfDays )
RETURN
    SUMX ( VALUES ( calendar[Date] ), DailyAmount )

I've tried to filter out BudgetDates using FILTER, but when I tried something like this:

CALCULATE(DATESBETWEEN (calendar[Date], FirstMdate, LastMdate ),calendar[isWorkingDay]=1)

I'm getting an error: "The function expets a table expression for argument ", but a string or numer expression was used."

I would appreciate any help here. Thanks!

[v-zhangti · Accepted solution]

Hi, @jajecko 

You can try the following methods. I briefly simulated some data that I hope will fit your situation.

WEEKDAY = WEEKDAY([Date],2)

isWorkingDay = IF([WEEKDAY]<=5,1,0)

Measure:

BudgetSplit = 
VAR _Budget =
    SUM ( 'Monthly budget'[Budget] )
VAR _workday =
    CALCULATE (
        COUNT ( 'Calendar'[isWorkingDay] ),
        FILTER (
            ALL ( 'Calendar' ),
            [isWorkingDay] = 1
                && [Month] = SELECTEDVALUE ( 'Calendar'[Month] )
        )
    )
RETURN
    DIVIDE ( _Budget, _workday )

Do you expect the results as shown in the figure?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jajecko]

Hi @v-zhangti 
Thanks for your reply. Your solution works fine but there are some issues with I'm strugling with.
First of all, I've figured out that when we have current month, we get working days to date, not the full month. 
I don't want to have future dates in my calendar table, So I've spend some time looking for solution and found this measure which returns full number of working days for current month.

NetWorkDays = 
VAR StartDate = DATE(YEAR(MAX('Calendar'[Date])),MONTH(MAX('Calendar'[Date])),1)
VAR EndDate = DATE(YEAR(MAX('Calendar'[Date])),MONTH(MAX('Calendar'[Date]))+1,1)-1

VAR Date1a = MINX ({startDate,EndDate},[Value]
)
VAR Date2a = MAXX ({startDate,EndDate},[Value]
)
VAR _Calendar = 
        ADDCOLUMNS(
            EXCEPT(
                CALENDAR(date1a,date2a),
                'Holidays'
            ),
            "_WeekDay",
            WEEKDAY([Date],2)
        )
RETURN
    COUNTX(
        FILTER(
            _Calendar,
            [_WeekDay] <6
        ),
        [Date]
    )
     

and it works fine but it shows proper data only for current month. When we'd like to check the working days for each month, then it returns wrong data. 

The other issue with your measure is that I'd like to show the difference between weekly sales and weekly budget on the line chart like this:

Unfortunately it doesn't work with given measure. 

I hope it makes sense;) Thanks inadvance for your help.

[stefani_vileva]

In this file, everything is working fine. 

I think you can try checking each variable if you had it right and then in the end you will know what is the problem.

[jajecko]

In this file everything works because there is no filter on working days.
Have you tried adding caluculate to VAR BudgetDates DATESBETWEEN with [isWorkingday]=1? 

[stefani_vileva]

I think I solved your problem.

Try to insert a column in the Date table with value 1 for all of the rows. Then use the following measure

CALCULATE(SUM(Dim_date[count]), DATESBETWEEN (Dim_date[Date] , FirstQTRDate, LastQTRDate ), Dim_date[isWorkingDay]=1)

[jajecko]

Dear Stefani, 
Sorry but could you be more precise where to put this measure?  I'd like to filter out BudgetDates var.

Thank you 

[stefani_vileva]

I think you it is best to put it in the Dim table, because you will be filtering dates from the same table.

[jajecko]

I'm afraid i dont get it;) 
I've added to the [count] column with "1" for each row. 

Right now I need to modify Var budgetDates in The formula: 

BudgetSplit = 
VAR FirstQTRDate =STARTOFQUARTER(Dim_date[Date])
VAR LastQTRDate=ENDOFQUARTER ( Dim_date[Date] )
VAR BudgetDates= DATESBETWEEN (
            Dim_date[Date],
            FirstQTRDate,
            LastQTRDate
        )
VAR BudgetToDivide =
    CALCULATE ( [TotalBudget], Dim_date[Date] IN BudgetDates )
VAR NumOfDays =
    COUNTROWS (BudgetDates)
VAR DailyAmount =
    DIVIDE ( BudgetToDivide, NumOfDays )
RETURN
  SUMX ( VALUES ( Dim_date[Date] ), DailyAmount )

 
And i'm not sure how to modify it because the given measure will return only the working days quantity.  What i need is to filter out the list of dates in BudgetDates VAR 

[stefani_vileva]

I think there is misunderstanding. At first I thought that you wanted to calculate the number of the days. If you need to filter the dates, you can put a slicer that will contain the date column and in which from and until date will be selected. Then, you can create variables that will calculate the minimum and maximum of the selected period and budget dates will be contained of the dates between this periods.

The formulas for min and max date should be the following:

VAR min_date = MINX(Dim_Date, Dim_Date[Date])
VAR max_date = MAXX(Dim_Date, Dim_Date[Date])

[jajecko]

Dear Stefania, 
Sorry about it, but I'm afraid that we still have something else in mind;)
The reason to use this [Split Budget] formula is to split the monthly budget to daily level. 

VAR "Budget dates" is the key here because it returns a list of dates to be used for calculation. The only thing I'd like to achieve is modify the var "budget dates" to get the list of dates but only for dates marked in calendar table with [isWorkingDay]=1
I hope it's clear now;) Sorry about the misunderstanding. 

[stefani_vileva]

I still am a little bit confused and don't know what is your goal..

If you want to get the list of all of the dates, you can always use the filter panel in which you can choose only the dates where isworkingday=1 to be shown.

[jajecko]

My goal is to split the budget for working days only, excluding weekends and holidays. Of course I can use the filter panel, but It's sort of workaround. I'd like to know how to filter it out within the formula;) 

[jajecko]

Hi, 
[isWorkingDay] column is a number format. Tried with text and quote. Also tried with true/false, but with no luck. 
Still getting the same error. 

[stefani_vileva]

Can you please share your file?

Thanks in advance.

[jajecko]

Unfortunately i cannot share my file but i can share a file on the basis of which I'm trying to solve this;) I've added my holidays table and [isWorkingDay] to calendar table.  
here it is: https://we.tl/t-qe1dMPpyWo

[stefani_vileva]

Hi,

Can you please check if the column isWorkingDay is number or text, and if it is a text then convert it to number, or just use the following dax:

CALCULATE(DATESBETWEEN (ALL(calendar), FirstMdate, LastMdate ),calendar[isWorkingDay]="1")

I hope this solves your problem.

Kind regards,

Stefani