Sum of Values based on Date Period

[newgirl]

Hi, everyone

I'm handling a Complaints PBI repoprt and below is a sample data. I have the following columns:

Complaint Number

Request Start

Closure Date

Initial Resolution Time (Closure Date - Request Start), which is a calculated column, and

Defined SLA

Complaint Number	Request Start	Closure Date	Initial Resolution Time (Closure Date - Request Start)	Defined SLA
1	7/16/2021	7/22/2021	6	3
2	7/16/2021	7/22/2021	6	3
3	7/16/2021	7/22/2021	6	3
4	7/23/2021	8/1/2021	9	5
5	7/19/2021	7/29/2021	10	8

So basically, the Report shows the time it takes for them to resolve a complaint. For example, Complaint 1 took 6 days to be resolved versus the SLA of 3 days.

Complaint 4 took 9 days to resolve versus SLA of 5 days.

The user handling complaints now wants me to deduct the number of holidays and weekends from the 'Initial Resolution Time' column. What I plan is:

- add another column to show the count of days to be deducted, label it as 'Less Days'

- add another column deducting 'Initial Resolution Time' less 'Less Days' to get 'Desired Resolution Time'

- Set up a separate calendar table for this with a Date Column and a mark column (value of 1 if holiday or weekend and 0 if not) and

Below is what I envision.

My question is what should the DAX formula be for the calculated column of  "Less Days" ?

[sm_talha]

I don't know about calculating this all with in a single measure, but I know a workaround for this, but it is not recommended for larde amount of data.

1. Add a Calendar table. 

Calendar = CALENDARAUTO( )

2. Create a Cross Join table to extract dates between Request Start and Closure Date.

Cross Joined Table =
FILTER (
CROSSJOIN ( 'Original Table', 'Calendar' ),
'Calendar'[Date] >= 'Original Table'[Request Start]
&& 'Calendar'[Date]
<= (
IF (
'Original Table'[Closure Date] <> BLANK (),
'Original Table'[Closure Date],
MAX ( 'Calendar'[Date] )
)
)
)

3. Add a calculated column to flag working days as 1 and Weekends as 0, I don't know about holidays in your region so you will have to add holidays by your self in the following formula or provide me the list of holidays. 

Weekend/Holiday =
IF ( FORMAT ( 'Cross Joined Table'[Date], "DDD" ) IN { "Sat", "Fri" }, 0, 1 )

4. Create your measure. (this will return the desired number of days, not "Less Day", I have subracted 1 from the measure so it, doesn't count the date when the complain was created)

Your Measure = SUM( 'Cross Joined Table'[Weekend/Holiday]) - 1

Please mark this reply as a solution if it helped you so other with similar problem can find the solution directly.

[newgirl]

Hello @sm_talha ! Thanks for the reply but I need the sum of the Holiday/Weekend as a calculated column.

I actually already have a table with date column and value column (1 for holiday/weekend and 0 if not),  I just haven't connected it yet to my report. I was't able to clarify that in my post.

[sm_talha · Accepted solution]

I get it now, please add your dates/calendar table with WeekOff/Holiday column (do not create any active relationship between date table and your fact table.
Then try adding a column by this formula to get your final column. (not less days). 

Final Column =
CALCULATE (
CALCULATE (
COUNTROWS ( 'Calendar' ),
FILTER (
'Calendar',
'Calendar'[Date] > MIN ( 'Original Table'[Request Start] )
&& 'Calendar'[Date] <= MAX ( 'Original Table'[Closure Date] )
)
),
ALLEXCEPT ( 'Original Table', 'Original Table'[Complaint Number] ),
'Calendar'[WeekDay/Off] = 0
)

If you must need Less Days column, then add a caculated column by following formula:

Less Day =
CALCULATE (
CALCULATE (
SUM ( 'Calendar'[WeekDay/Off] ),
FILTER (
'Calendar',
'Calendar'[Date] > MIN ( 'Original Table'[Request Start] )
&& 'Calendar'[Date] <= MAX ( 'Original Table'[Closure Date] )
)
),
ALLEXCEPT ( 'Original Table', 'Original Table'[Complaint Number] )
)

Once again, i do not know about the holidays in your region so I am just excluding weekends.

[newgirl]

Hi @sm_talha ! THank you so much, the formula worked! 

I'd like to know what is the purpose of this portion? 

ALLEXCEPT ( 'Original Table', 'Original Table'[Complaint Number] )

[sm_talha]

I am glad I could help. 

This ALLEXCEPT part of the formula removes all context filters in the table except applied filters to the complaints column, so it will evaluate through each complaint number and return our less days or final results for each complaint separately, otherwise you will get total of less days/final column. 

[amitchandak]

@newgirl , A new column of measures like

New column =

[Initial Resolution Time] -[Defined SLA]

New measure =

Sumx(Table,[Initial Resolution Time] -[Defined SLA])

or

New measure =

Sum([Initial Resolution Time]) -sum([Defined SLA])

If you need work day then you need to corrrect Initial Resolution Time

Initial Resolution Time

Work Day = COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Table[Request Start],Table[Closure Date] ),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1))

then again above formulas

refer if needed - How to calculate Business Days/ Workdays, with or without date table: https://youtu.be/Qv4wT8_P-AA

[newgirl]

Hi @amitchandak ! Thanks for your reply but I just want to clarify a few things.

- The count of the days is not dependent on the 'defined SLA' or anything. It's just another column from the database that lets the reader compare the Resolution Time with SLA. In hindsight, I shouldn't have included it in the post, I guess.

- The only question I have is the DAX formula to be used for the calculated column of "Less Days", assuming that I have my own calendar table ready already and that calendar table already has a column that has a value of 1 for holiday/weekend and 0 if not.