Remove weekends in a formula

Question

I want to remove weekends from a calculated column calculation. I am having a formula which calculate daily target:

Daily MAL Target = [MAL_Qtarget_A] /
                   (ENDOFQUARTER(Marketing_targets_MALMEL[Date]) -
                   ENDOFQUARTER(PREVIOUSQUARTER(Marketing_targets_MALMEL[Date])))

In the marketing Targets table thers a seperate column to identify whether the date is working day or weekday as 1 and 0.

IsWorkDay = SWITCH(WEEKDAY([Date]),1,0,7,0,1)

I want to add "IsWorkingDate=1" to the above Daily Mal Target formula. It is a calculated column. I have tried so many ways but could not do it.

Can anyone help me on this?

score 1 · Answer 1 · answered Jan 31 '20 at 06:59

1

You can use this DAX expression to check for weekdays:

= IF(OR(WEEKDAY([Date]) = 1, WEEKDAY([Date) = 7), "Calculation for Weekdays", "Calculation for Mo to Fr")

answered Jan 31 '20 at 06:59

Strawberryshrub

3,301
2
11
20

score 0 · Answer 2 · answered Jan 31 '20 at 17:14

If it's a calculated column, you can use an if condition to make the value 0 or blank for weekends:

Daily MAL Target = IF(WEEKDAY(Marketing_targets_MALMEL[Date]) in {1,7},
                   BLANK(),
                   ([MAL_Qtarget_A] /
                   (ENDOFQUARTER(Marketing_targets_MALMEL[Date]) -
                   ENDOFQUARTER(PREVIOUSQUARTER(Marketing_targets_MALMEL[Date]))))

Hope this helps.

Remove weekends in a formula

2 Answers2