Forum Discussion
Solved
Exclude weekend using dateadd
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....
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))
Hi amitchandak,.
I can't see how this function could help me in this case. What did you think?
regards
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))
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,
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.