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

Hi v-xicai , 

 

"please put measure Filter to the Visual Level Filter of table visual displaying column Datediff, and select option "is not blank" - When I do this, it filters my table to only 12 rows (which is incorrect ). I checked the relationships and all seems to be fine. 

One other thing that I would need to do is to get the DATEDIFF excluding weekends, can you advise please? 

Thank you!

 

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

Hi Anonymous ,

 

So the criteria as you said is to filter the weekends out, right?

 

do not consider this row (for eg: FP#00119) for the selected month if:

    - Date_Presented is > end of the selected month

    - Date_Demande is > end of the selected month - 21 Days

    - Date_Presented is < Beginning of the Quarter that the selected month belongs to.

 

Best Regards,

Amy

  • 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.