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

     

  • MarceloPereira's avatar
    MarceloPereira
    Frequent Visitor
    6 years ago

    Hi amitchandak,.

     

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

     

    regards

    • hnguy71's avatar
      hnguy71
      Super User
      6 years ago

      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's avatar
        MarceloPereira
        Frequent Visitor
        6 years ago

        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,