cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MattAnnandale Frequent Visitor
Frequent Visitor

DAX - Calculating SLA's

Hi all, 

 

Got an odd one that I could use somehelp with please. I have a set of data that has an [Enquiryreceived] datetime and a [Closeddatetime] amongst other bits and have 3 possible Service Level Agreement categories it could fall in to. 

 

1) If the date is before August 1st 2019 then it's SLA X

2) If it's after August 1st but before Oct 1st it's SLA Y 

3) If it's equal to or after October 2019 and received before 2PM it must be closed before 5. If after 5pm then it gets 24 hours and if it's received after 5PM on a Friday or on a weekend then it has to be treated as if it came in on the following Monday at 8AM. 

 

Now, I can handle 90% of this but the 'after 5pm Friday || Saturday || Sunday' then treat as Monday 8am is killing my brains. 

 

Anyone come up against anything similar at all or able to offer advice? 

 

TIA

2 REPLIES 2
kentyler New Contributor
New Contributor

Re: DAX - Calculating SLA's

The "treat as" is a clue.

Maybe create a date or date/time column that holds the date the "treat as date".

Then the calculate of whether if falls into SLA 1 or 2 becomes simple.

I don't know if you're using a date/time calendar table, which might be one place to calculate the "treat as" date, or if it might work better to calculate it in a query as a calculated column in DAX. Both ways would separate the calculate if, if logic from the decision of which SLA the enquiry should fall under

MattAnnandale Frequent Visitor
Frequent Visitor

Re: DAX - Calculating SLA's

Thanks for the reply - some colleagues and I managed to troubleshoot this by changing the way we interpreted the question. Instead of considering it to have come in at 08:00 Monday we knew that it had to be closed by 5ppm Monday to be within SLA. The change in perspective opened it up for interrogation. 

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)