SLA Calculation based on multiple criteria

Anonymous I looked at your data and not sure what you mean #ref in your post , like when #ref selected? Can you in sample excel sheet put a column with expected result.

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

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.