Exclude weekend using dateadd

[MarceloPereira]

Hi Everyone.

I need help to compare the margin of sales.

When I compare Monday's sales margin with the day before, the DATEADD function searches for Sunday's margin, where I don't have sales. I need to compare Monday's margin to Friday's margin, excluding the weekend in my calculation.

In my case, number 6 is Saturday and 0 is sunday.

There is any way to do it?

Best regards

[amitchandak]

Try if Earlier can work in this case

https://community.powerbi.com/t5/Desktop/Explanation-of-the-EARLIER-formula/td-p/529469

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[MarceloPereira]

Hi @amitchandak,.

I can't see how this function could help me in this case. What did you think?

regards

[hnguy71]

Hi @MarceloPereira ,

Try this as a measure:

PreviousMargin = 
var _Date = MAX(YOUR_TABLE[Date])
var _isWeekday = WEEKDAY(_Date, 2)
RETURN
SWITCH(TRUE(),
_isWeekday = 1, CALCULATE(MAX(YOUR_TABLE[Margins]), YOUR_TABLE[Date] = _Date - 3),
_isWeekday > 1 && _isWeekday <= 5, CALCULATE(MAX(YOUR_TABLE[Margins]),  YOUR_TABLE[Date]= _Date - 1))

[MarceloPereira]

Hi @hnguy71 ,

I understand your logic, but my table has other dimensions that should be taken into account at this point in the calculation.
Because of this, I am receiving the following message:
The MAX function only accepts a column reference as the argument number 1.

regards,

[hnguy71]

Hi @MarceloPereira ,
You need to adjust the measure. YOUR_TABLE needs to be replaced with whatever table name you're currently using with your date field.  The same goes for your other fields. If the information is not sensitive, provide a PBIX and I can help you further.

[MarceloPereira]

Hi @hnguy71 ,

follow my pbix: Example file 

regards

[hnguy71 · Accepted solution]

Hi @MarceloPereira ,
Try this instead:

C_MargemTeste = 
var _Date = MAX(DIM_CALENDARIO[Date])
var _isWeekday = WEEKDAY(_Date, 2)
RETURN
SWITCH(TRUE(),
_isWeekday = 1, CALCULATE([C_Margem], DIM_CALENDARIO[Data] = _Date - 3),
_isWeekday > 1 && _isWeekday <= 5, CALCULATE([C_Margem],  DIM_CALENDARIO[Data] = _Date - 1))

[Jwheels27]

How would you account for holidays using this?

[hnguy71]

Hi @Jwheels27 ,

You would probably need a column or a dimension that says that particular day is a holiday. From there it's a similar logic where you find out if that holiday lands on a weekday or not.

[MarceloPereira]

@hnguy71 ,

Wowww... That worked!
Thanks a lot for the help

best reagards