Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Hiding values in Matrix for lower levels

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

Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    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

1 Reply

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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