DAX- IF Statement and Date Columns with days added

[cottrera]

Hi 

I have the following tables.

FACT

Ref	Team	Due Date	Completion Date
1	Emergency	12/12/2023	23/12/2023
2	Emergency	23/12/2023	01/01/2024
3	Urgent	01/12/2023	17/01/2024
4	Routine	01/01/2024	04/01/2024
5	Urgent	04/01/2024	17/01/2024
6	Emergency	04/01/2024	02/02/2024
7	Routine	01/01/2024	15/01/2024
8	Routine	17/01/2024	04/02/2024
9	Urgent	15/01/2024	04/02/2024
10	Urgent	17/01/2024	03/02/2024

And a calendar table with the following

Date	Day	Is_Holiday	Is_Weekend
05/02/2024	Mon	0	0
06/02/2024	Tue	0	0
07/02/2024	Wed	0	0
08/02/2024	Thu	0	0
09/02/2024	Fri	0	0
10/02/2024	Sat	0	1
11/02/2024	Sun	0	1

I have a measure similar to this

SLA =IF(RepairsCoreRR[Date Completed] > = RepairsCoreRR[Due Date] , TRUE() , FALSE())

Our business is trying to see how the SLA would look if the Due Dates were extended by a set abount of days. These new due dates would be different depending on the Team for example

 

Team	Tir 1	Tir 2
Emergency	= Due Date + 1	= Due Date + 2
Urgent	= Due Date + 10	= Due Date + 20
Routine	= Due Date + 20	= Due Date + 40

Ineed help creating two DAX measures called Tir 1 and Tir 2 applying this logic

SLA =IF(RepairsCoreRR[Date Completed] > = RepairsCoreRR[Due Date] , TRUE() , FALSE())

Possibly using a SWITCH function that check for the Team and then applied the added number of days to the due date before carrying out the IF function.

Some added complexity is that the  dded number of days to the due date must be working days , which can come from the calendar table

thank you RIchard

[Anonymous · Accepted solution]

Hi @cottrera 

For your question, here is the method I provided:

Here's some dummy data

"calendar"

"FACT"

Create measures. You can use NETWORKDAYS to filter working days.

Due_Date_1 = 
var _work = NETWORKDAYS(MIN('FACT'[Due Date]), MAX('FACT'[Due Date]))
var _team = SELECTEDVALUE('FACT'[Team])
VAR _DATE=MAX('FACT'[Due Date])
var New_due_1 = IF(_work = 1,
    SWITCH(
        _team,
    "Emergency",_DATE+1,
    "Urgent", _DATE+10,
    "Routine", _DATE+20
    ),
    BLANK()
)
RETURN New_due_1

Due_Date_2 = 
var _work = NETWORKDAYS(MIN('FACT'[Due Date]), MAX('FACT'[Due Date]))
var _team = SELECTEDVALUE('FACT'[Team])
VAR _DATE=MAX('FACT'[Due Date])
var New_due_2 = IF(_work = 1,
    SWITCH(
        _team,
    "Emergency",_DATE+2,
    "Urgent", _DATE+20,
    "Routine", _DATE+40
    ),
    BLANK()
)

RETURN New_due_2

Tri1 = IF(SELECTEDVALUE('FACT'[Completion Date]) >= [Due_Date_1], TRUE(), FALSE())

Tri2 = IF(SELECTEDVALUE('FACT'[Completion Date]) >= [Due_Date_2], TRUE(), FALSE())

Here is the result.

Regards,

Nono Chen

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

[cottrera]

Thanks for your quick response Nono Chen. Your code works fine.

Richard

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.