April 15, 2025 at 2:44 pm
Hi -
Currently, I have this DAX coded in Power BI
Ticket Cutoff Status =
VAR TimeOnly = TIME(HOUR([Remedy Submitted Date]), MINUTE([Remedy Submitted Date]), SECOND([Remedy Submitted Date]))
RETURN
SWITCH(
TRUE(),
TimeOnly < TIME(13, 0, 0), "Prior to Cutoff",
TimeOnly >= TIME(13, 0, 0) && TimeOnly <= TIME(14, 0, 0), "Grey Zone",
TimeOnly > TIME(14, 0, 0), "Prior the Cutoff"
)
Since I need to automate the Power BI fully using the MS SQL coding and the database I pull data from has the EPOCH date/time. I was able to figure this out for days, etc for other columns...
DATEDIFF(day, DATEADD("hh", -5, DATEADD("S", Submit_Date, 'January 1, 1970')),DATEADD("hh", -5, DATEADD("S", Completed_Date, 'January 1, 1970'))) as 'WO Age',
DATEDIFF(day, DATEADD("hh", -5, DATEADD("S", Submit_Date, 'January 1, 1970')),GETDATE()) as 'Aging',
CASE WHEN DATEDIFF(day, DATEADD("hh", -5, DATEADD("S", Submit_Date, 'January 1, 1970')),DATEADD("hh", -5, DATEADD("S", Completed_Date, 'January 1, 1970'))) >= 30 Then 'Yes'
WHEN DATEDIFF(day, DATEADD("hh", -5, DATEADD("S", Submit_Date, 'January 1, 1970')),DATEADD("hh", -5, DATEADD("S", Completed_Date, 'January 1, 1970'))) < 30 Then 'No'
END AS 'Over 30 Days'
BUT I cannot figure out the cut off time part like the DAX code in SQL.
So basically what I need is the SQL for:
tickets submitted prior to 1 pm = Prior to Cutoff
Between 1 pm and 2 pm = Grey Zone
After 2 pm = Past the cutoff
Thank you,
Holly
April 16, 2025 at 3:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
April 17, 2025 at 10:59 am
Hi Holly,
You're on the right track with converting from EPOCH time and using DATEADD! To replicate the logic from your DAX code in SQL, you can extract the time portion from your converted Submit_Date and then use a CASE expression like this:
If you're already adjusting for time zones with DATEADD("hh", -5, ...), just replace the AT TIME ZONE part with your existing logic. The key is to extract just the time component using CAST(... AS TIME).
Hope this helps! Let me know if you need help adapting it to your exact structure.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply