SLA Calculation based on multiple criteria

[Anonymous]

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 follwoing two dates.

Table:

t_Data

Fields: 

- Date_Requested

- Date_Presented

- #ref_date

Criteria to consider:

- If selected Date_Presented is < beginning of the quarter in which #ref selected is, do not consider 

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

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

Data Sample:

https://we.tl/t-0KAMMjURHq

Thank you guys. 

[Anonymous]

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 follwoing two dates.

Table:

t_Data

Fields: 

- Date_Requested

- Date_Presented

- #ref_date

Criteria to consider:

- If selected Date_Presented is < beginning of the quarter in which #ref selected is, do not consider 

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

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

Data Sample:

https://we.tl/t-0KAMMjURHq

Thank you guys. 

[v-xicai]

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]

@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]

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]

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]

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]

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]

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.

[v-xicai]

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]

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 

[parry2k]

@Anonymous it is still very confusing what you want and how you worded your question. You are sharing sample data but not expected output, can you put some notes in excel file based on sample data what actually you are looking for.

Please read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

@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

[parry2k]

@Anonymous you alredy shared that file, thanks for sharing again Based on your data in the sheet, can you put another sheet with expected output.

[parry2k]

@Anonymous what you mean by "selected month" in following

2) 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.

[Anonymous]

Hi @parry2k 

Basically, this will be placed in the main Dashboard, and the main filter there will be "Date", therefore what I would like to achieve is basically if a manager selects "March", the SLA 01 will have those criteria in consideration based on the selected month. Please do let me know if this make any sense ? 

Thank you for your help! 

Nelson 

[parry2k]

@Anonymous in this whole time, it is first time you mentioned what is "Selected month", now it make sense

[Anonymous]

@parry2k my apologies for the confusion. Would you be able to help me build the SLA column with the criteria mentioned? 

Thanks a lot! 

[Anonymous]

@v-xicai can you support please?

Thanks. 

[Anonymous]

@parry2k  I have commented in the sheet, hope it clarifies. Once again, thank you so much! 

Data :

https://we.tl/t-Yzac8eD7HH

[Anonymous]

Hi Amy, 

I am preparing the sample and will share with you.

Thank you very much. 

[parry2k]

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