Forum Discussion
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 to my date column. So basically if the date shows 3/14/22 but the time on the same row is 8:00:00 PM for example, I would like to have the new column calculate the date to show 3/15/22.
I am new to DAX and have been trying if(and to try to add the day if it is between the times mentioned but am getting syntax and other errors. For example below this code just returns "Error"
if([Time of Cancel]>"6:59:59 PM") and ([Time of Cancel]<"12:01:00 AM")
then ([#"Payment Cancellation Date/Time"]+1)
else ([#"Payment Cancellation Date/Time"])
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.
4 Replies
- mh2587
Super User
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"])
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"])
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.You're amazing -thanks so much!
