Hello All,
Full disclosure: I am not a programmer. I am newer to Powerbi and all knowledge is self taught so I will not be offended if you explain in laymans terms.
I am having an issue finding a formula for networking days that picks up negative days. The formula I have below works great for positive sums but returns 0 for negative sums. I am looking for a negative output for the formula in green. Unfortunately, I am not able to reverse the formula because there are instances where there are positive summations between the two dates in which case the data would then become skewed. I am working in the general report queries for this specific formula. But if someone knows a networking day formula in the query editor function that is able to reference the 'Table - Date' table I would be interested (I may actually prefer) in that as well. Any and all suggestions appreciated.
IF('All Reports'[SLA]="PA1-Reg>5D_Prior",sumx (FILTER('Table - Date','Table - Date'[Date].[Date] <= 'All Reports'[Receipt/Creation Date].[Date] && 'Table - Date'[Date].[Date] >= 'All Reports'[EffectiveDate].[Date]), 'Table - Date'[Workday]),
'All Reports'[Receipt/Creation Date].[Date] = creation date.
'All Reports'[EffectiveDate].[Date]) = effective date. Ideally, should be less than creation date. Not all are.
'Table - Date','Table - Date'[Date].[Date] = Reference Date table.
'Table - Date'[Workday]) = a column showing “1” for working day and “0” for non-working day.
Complete Formula:
SLA Days all calc = IF('All Reports'[SLA]="PA5-HRMS-Hire<2D",Sumx (FILTER('Table - Date','Table - Date'[Date].[Date] >= 'All Reports'[FirstApprovalDate] && 'Table - Date'[Date].[Date] <= 'All Reports'[HRMSEntryDate]), 'Table - Date'[Workday]),IF('All Reports'[SLA]="PA6-HRMS-Term<3D",Sumx (FILTER('Table - Date','Table - Date'[Date].[Date] >= 'All Reports'[FirstApprovalDate] && 'Table - Date'[Date].[Date] <= 'All Reports'[HRMSEntryDate]), 'Table - Date'[Workday]),IF('All Reports'[SLA]="PA7-HRMS-Change",Sumx (FILTER('Table - Date','Table - Date'[Date].[Date] >= 'All Reports'[FirstApprovalDate] && 'Table - Date'[Date].[Date] <= 'All Reports'[HRMSEntryDate]), 'Table - Date'[Workday]),IF('All Reports'[SLA]="Hours to Employee Save",'All Reports'[SLA Days Kronos],IF('All Reports'[SLA]="Hours to Sign-off",'All Reports'[SLA Days Kronos],IF('All Reports'[SLA]="Hours to Approval",'All Reports'[SLA Days Kronos],IF('All Reports'[SLA]="PA1-Reg>5D_Prior",sumx (FILTER('Table - Date','Table - Date'[Date].[Date] <= 'All Reports'[Receipt/Creation Date].[Date] && 'Table - Date'[Date].[Date] >= 'All Reports'[EffectiveDate].[Date]), 'Table - Date'[Workday]),IF('All Reports'[SLA]="PA3-FCEntry<2D",sumx (FILTER('Table - Date','Table - Date'[Date].[Date] <= 'All Reports'[CreationDate].[Date] && 'Table - Date'[Date].[Date] >= 'All Reports'[Receipt/Creation Date].[Date]), 'Table - Date'[Workday]),IF('All Reports'[SLA]="PA4-Appr<1D",sumx (FILTER('Table - Date','Table - Date'[Date].[Date] <= 'All Reports'[FirstApprovalDate] && 'Table - Date'[Date].[Date] >= 'All Reports'[CreationDate].[Date]), 'Table - Date'[Workday]),if('All Reports'[SLA OB]=1,sumx (FILTER('Table - Date','Table - Date'[Date].[Date] <= 'All Reports'[Completed Date].[Date] && 'Table - Date'[Date].[Date] >= 'All Reports'[receivedDate].[Date]), 'Table - Date'[Workday]),IF('All Reports'[SLA]="OIE1-Submit<60D",Sumx (FILTER('Table - Date','Table - Date'[Date].[Date] >= 'All Reports'[Creation Date OIE].[Date] && 'Table - Date'[Date].[Date] <= 'All Reports'[Report Submitted Date].[Date]), 'Table - Date'[Workday]),IF('All Reports'[SLA]="OIE2-Appr<5D",Sumx (FILTER('Table - Date','Table - Date'[Date].[Date] >= 'All Reports'[Report Submitted Date].[Date] && 'Table - Date'[Date].[Date] <= 'All Reports'[Pending payables approv date OIE].[Date]), 'Table - Date'[Workday]),IF('All Reports'[SLA]="OIE4-Audit<2D",'All Reports'[OIE4-Audit calc],if('All Reports'[SLA Days OB]>0,'All Reports'[SLA Days OB],BLANK()))))))))))))))
