Forum Discussion
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. 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
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))
10 Replies
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/823601Hi 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))
