Forum Discussion

MarceloPereira's avatar
MarceloPereira
Frequent Visitor
6 years ago
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....
Need Help
  • hnguy71's avatar
    hnguy71
    6 years ago

    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))

     

hnguy71's avatar
hnguy71
Super User
6 years ago

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))

 

  • MarceloPereira's avatar
    MarceloPereira
    Frequent Visitor
    6 years ago

    hnguy71 ,

     

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

     

    best reagards

  • Jwheels27's avatar
    Jwheels27
    New Member
    6 years ago

    How would you account for holidays using this?

  • hnguy71's avatar
    hnguy71
    Super User
    6 years ago

    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.