HELP, Why doesn't DATEADD() work with a column that is equal to Today() or Now()?

[Y-Hernandez99P]

Good morning, I hope you're all right.

I have a little doubt and it is: Why when I use THE DATEADD() functions when a column or variable is declared with the NOW() or Today functions?

I have to make a report which brings me data of some orders from the organization from the day before 6pm until the current day at 6pm... From the BI slicers I saw that I couldn't do the specific filters as I wanted, so I was doing a support column in DAX where if there is data with current date and its time is less than or equal to 18 (6pm) then put a 1, if you put 0. In case if the day is not equal to the current day, check if the day of the row is equal to the day of the previous date, then check if the time of the row is greater than or equal to 18.... Seeing that I didn't have the expected results, with a check column, I set the current date, and in the other the DATEADD function which it pulls out the previous day, and when checking the last column was empty.
Do you have any choice of how to make these validations?

ValidarFecha = 
var diafechaCol = DAY([FechaDoc])
var diafechaAct = DAY(TODAY())
var fechadiaAnt = DATEADD(IV_PEDIDOS8D[APOYO_fechaActual];-1;DAY)
var diafechaAnt = DAY(fechadiaAnt)
var horafechaAct = [HoraCreacion]
Return
IF(diafechaAct = diafechaCol;
    IF(horafechaAct <= 18; 1;0)
        ;IF((diafechaAct = diafechaAnt);
            IF(horafechaAct >= 18;1;0)
        ;0)
)

Here the column on the left is equal to NOW() (same as I did with today and it still doesn't work), and next to it is the column that is equal to DATEADD(IV_PEDIDOS8D[APOYO_fechaActual];-1;day) and goes blank.

[v-yingjl · Accepted solution]

Hi @Y-Hernandez99P ,

Time-intelligence-functions like DateAdd() needs a separate continuous date table. In this issue, you can create a column like this:

Column =
VAR _lastday =
    DAY ( TODAY () - 1 )
VAR _today =
    DAY ( TODAY () )
VAR _day =
    DAY ( 'Table'[date] )
VAR _hour =
    HOUR ( 'Table'[date] )
RETURN
    IF (
         ( _day = _today
            && _hour <= 18 )
            || ( _day = _lastday
            && _hour >= 18 ),
        1,
        0
    )

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Y-Hernandez99P , dateadd need a continuous date.

You can use date in place of date add.

https://community.powerbi.com/t5/Community-Blog/Date-as-Dateadd-Decoding-Date-and-Calendar-5-5-Power...