Forum Discussion

jenniferhoward's avatar
jenniferhoward
Regular Visitor
4 years ago
Solved

Calculation if time falls between 7pm-midnight

Hi I am having trouble getting this to work in DAX.  I have a time column and need to calculate a new date based on the time. SO if the time if >7:00:00 PM and < 12:00:00 AM, I need to add one day ...
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi jenniferhoward ,

     

    1. Create a calculated column by using DAX:

     

    Column = IF([time]>TIME(18,59,59) ,DATEADD('Table'[date].[Date],1,DAY),[date])

     

     

     

    2. Create a custom column in Power Query:

     

    if [time]> Time.FromText("18:59:59") then Date.AddDays([date],1) else [date]

     

     


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

mh2587's avatar
mh2587
Icon for Super User rankSuper User
4 years ago

if([Time of Cancel]>"6:59:59 PM") && ([Time of Cancel]<"12:01:00 AM"),DATEADD(DateTable[#"Payment Cancellation Date/Time"],+1,DAY),DateTable[#"Payment Cancellation Date/Time"])

  • jenniferhoward's avatar
    jenniferhoward
    Regular Visitor
    4 years ago

    I haven't been able to get this to work.  Now trying it Power Query and the error is that it can't compare dates with < > operators.  This is what I was trying to do.

     

    if [Related Payment Date]>"6:59:59 pm" and [#"Payment Cancellation Date/Time"]<"12:00:00 am" then [#"Payment Cancellation Date/Time"]+1
    else [#"Payment Cancellation Date/Time"])