Comparing working days to expected completion date

[Stuartm1983]

Hi,

I'm looking to do the following and would love to have some help on this as I don't know how to crack it yet.

Basically I have a complaint table as follows:

Date (Complaint Made) | Assigned to |Days to Resolve | Date Complaint Resolved | Working Day

1/5/2019                                     Mary                    3                                       4/5/2019                                           1

1/5/2019                                     Bob                       3                                        4/5/2019                                          1

2/5/2019                                     Mary                    3                                       4/5/2019                                           1

2/5/2019                                     Bob                       3                                        4/5/2019                                          1

3/5/2019                                     Bob                       3                                        4/5/2019                                          0

4/5/2019                                     Bob                       3                                        4/5/2019                                          1

.

.

...

This is built up of 2 tables a complaint dataset and a working day dataset with a date and a binary flag on it only. I merged it using the Date on the left in the modelling section.

Now what I want to do is compare the 'days to resolve' to the complaint resolved date. However taking into account days which are not working days and discounting them.

Therefore if a manager has taken too long to resolve a complaint accounting for holidays I want that to be summarised and highlighted in a graph or table. Who and by how many days over the date.

Could anyone please help me?

Thanks 🙂

[v-kelly-msft]

Hi @Stuartm1983 ，

I'm not sure whether you need a result as below：

The dax expression is as below:

Column = 
var a=DATEDIFF('Table'[Date (Complaint Made)],'Table'[Date Complaint Resolved ],DAY)
Return
IF('Table'[Working Day]<>0,'Table'[Days to Resolve ]-'Table'[Working Day],'Table'[Days to Resolve ]-a)

If not.pls advise me your expected result.

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Stuartm1983]

Hi Kelly,

Thanks for your reply.

I don't think I articulated my problem properly.

I have the complaints table. 1 row per complaint and a working day look up table (a row per day and set up as a date table), joined on 'Date'.

For each row in the dummy table above, I would like to lookup the 1/0's in the working date look up table and sum up the number of working days between the date and resolved date (adding the 1s). Column is called 'Working Day'

In practice I have replaced Resolved date with today's date if it hasn't been resolved.

I hope this makes sense.

Thanks.

🙂

[JirkaZ]

This will give you a nice idea on how to tackle this:

https://www.sqlbi.com/articles/counting-working-days-in-dax/