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 ,

You can create column Datediff to calculate days between Date_Requested and Date_Presented in t_Data table.

 

Datediff = DATEDIFF(t_Data[Date_Requested],t_Data[Date_Presented],DAY)

 

Then, create measure Filter, and put measure Filter to the Visual Level Filter of visual displaying column Datediff, and select option "is not blank".

 

Filter = var quarter= ROUNDUP(MONTH(MAX(t_Data[Date_Presented]))/3,0)

var day_presented=DAY(MAX(t_Data[Date_Presented]))

var day_requested=DAY(MAX(t_Data[Date_Requested]))

var day_21=EOMONTH(MAX(Table2[Date]),0)-21

return IF (

COUNTROWS ( ALLSELECTED ( Table2[Date] ) ) < 1,BLANK (),

IF(quarter<(ROUNDUP(MONTH(MAX(Table2[Date]))/3,0))||day_requested>day_21||day_presented>EOMONTH(MAX(Table2[Date]),0),BLANK(),1))

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

When make some selections in slicer, return result showing picture below.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

 

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

v-xicai  parry2k thank youso so much! 

It really works the way it's supposed to. 

As you can see below, I have filtered out Saturdays+ Sundays. 

However, the DATEDIFF between "Date Demande" & "Date Presented" is still summing all days (including weekends and holidays). How can I tackle this, even if I have the filter DayofWeekNumber set only for weekdays. 

 

Thank you ! 


  • 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