Hiding values in Matrix for lower levels

[Anonymous]

Hi, I need hel\p with calcualting processing time.

I have  SubmissionDateTime  and  DecisionDateTime

Now business wants to find all the application processing times with time buckets. How many Hours it took to process excluding Holidays/Weekends defined in DateDim table

Ex: If a Customer submitted application on 02/06/2020 at 3:00 PM (Thursday)
and
accepted on 02/10/2020 11:00 AM

So Totoal Hours for this application will be:

Thursday: 12-3 = 9 Hours (Thursday EOD 11:59 PM) 
Friday: 24 Hours
again it should check for next working day before DecisionDate which is Monday 12 AM - Here Clock stops till next workind day
Monday: 12 AM to 11 AM - 11 Hours

Total: 9 + 24 + 11 = 44 Hours

Reports are based on : < 24 Hours, 24-48 Hours, > 48 Hours.

Note: requirement is to consider 24 hours as Working hours on a Working Day.

DAX or SQL any solution is works for me.

Thanks in advance

[v-lili6-msft · Accepted solution]

hi  @Anonymous 

For your case, you could use this way as below:

1. create a total hours column as below:

Total hours = 
DATEDIFF ( [SubmissionDateTime], [DecisionDateTime], HOUR ) - 
24* COUNTROWS (  FILTER (
                'Date',
                'Date'[Date] >= 'Table'[SubmissionDateTime]&& 'Date'[Date] <= 'Table'[DecisionDateTime]
                    && ( 'Date'[Weekday] = 6 || 'Date'[Weekday] = 7 )
            )
        )

You could just add column in DateDim table that if this date is Holidays/Weekends, then use it in formula conditional.

Regards,

Lin