Forum Discussion

ToddH's avatar
ToddH
New Member
8 years ago

Help Networking day formula needed

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()))))))))))))))

 

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    Text colouring hasn't appeared in the post.. I'm assuming the formula you are speaking of is this one?
    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])

    Should the below be returning 1 for working day and -1 for non-working day?

    'Table - Date'[Workday]) = a column showing “1” for working day and “0” for non-working day.

     

    I'd need a bit more information on the formula in order to help.

  • ToddH Hi, looking at the code you are using Kronos? Can I ask if you have brought that in through an API? Thanks


    Ross