Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

SLA Calculation based on multiple criteria

Hi Community,    I really need help in finding the best approach to come to a solution to calculate the DATE DIFF days for each line/ #ref.  Calculate average of Date Diff days between the fol...
General Comment
Need Help
Show and Tell
Tips and Tricks
Anonymous's avatar
Anonymous
Not applicable
7 years ago

This criteria works fine but the DATEDIFF calc is summing up all days, including weekends.

I have created a isWorkDay flag, however it doesn't seem to work, Can you help me overcome this?

 

 

Thanks v-xicai 

v-xicai's avatar
v-xicai
Community Support
7 years ago

Hi Anonymous ,

 

You can create calendar table DateDim , and create column IsWeekDay to judge the day if weekday or not. Then create column Datediff for weekdays in table t_Data to replace my original column [Datediff].

 

DateDim = CALENDARAUTO()

 

IsWeekDay = SWITCH(WEEKDAY(DateDim[Date],2),6,0,7,0,1)

 

Datediff for weekdays = CALCULATE(SUM(DateDim[IsWeekDay]),DATESBETWEEN(DateDim[Date],t_Data[Date_Requested],t_Data[Date_Presented]))

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Here is my test pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EZv4vef4wwNFhuSlZd8fqw8B2LTT-nlMFYA8nzGXOjgFzQ?e=1ld0k7

 

Best Regards,

Amy

 

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