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
v-xicai's avatar
v-xicai
Community Support
7 years ago

Hi Anonymous ,

 

After you created measure Filter, please put measure Filter to the Visual Level Filter of table visual displaying column Datediff, and select option "is not blank".

 

Best Regards,

Amy

 

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

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.