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 nelson_marques,

 

From your description, I try to clarify the scenario: You may have another table2 which has two fields #ref_date and REF_ID, the #ref in criteria above is referred to the #ref_date(Date type) in another table2 which will be a slicer, you need the criteria to filter the whole first table t_Data which needs to create column (calculate the DateDiff between Date_Requested and Date_Presented). Right? If yes, the criteria is like this:

 

- If selected Date_Presented is < beginning of the quarter of #ref_date in which #ref selected is, do not consider (don't show this rows of another table2 in report?)

- If selected Date_Requested is > selected month of #ref_date in which #ref selected is, do not consider

- If (Date_Requested > (end of the month of #ref_date - 21 days) & (Date_Presented > End of the month of #ref_date)) , do not consider

 

Or, could you please share your more detail sample data or desired screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

 

Best Regards,

Amy

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Amy, 

    I am preparing the sample and will share with you.

    Thank you very much. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi v-xicai ,

     

    You did get to the solution,  can you help me construct the function with all the criterias?

     

    Sample of the real dates and refs : 

    https://we.tl/t-jOlNepI0mk

     

    Thank you so much! 

    Nelson 

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        parry2k Thank you for reaching out and try to help me. 

        Sample of the real dates and refs : 

        https://we.tl/t-jOlNepI0mk

         

        based on this dataset, the criteria to achieve this SLA metric is:

        - If selected Date_Presented is < beginning of the quarter which #ref selected is, do not consider (don't show rows)

        - If selected Date_Requested is > selected month in which #ref selected is, do not consider

        - If (Date_Requested > (end of the month of #selected month- 21 days) & (Date_Presented > End of the month of #selected month) , do not consider