Calculating the Average Days Difference

[LewisH]

I have a table called 'Put Away headers' and two fields Called 'Assignment Date' and 'Complete Date'. I would like to calculate an overall average of the days difference between these, excluding weekends. Please explain simply I'm new to this. 

[Anonymous · Accepted solution]

The easiest way to do this is to create a calculated column in combination with a date table. You will need a date table because you will need all dates in the specific period. You can either create a date table in your database, import it (and create it in Excel) or dynamically using DAX. For the last one, see for example: https://www.agilebi.com.au/blog/power-bi-date-dimension. 

You will need to create a column (For exampe 'Workingday' in this date table where you specify for each day if it's a weekday. The column should contain a 0 or a 1 (1 for weekdays and 0 for weekenddays). 

Then, your create a calculated column with the following formula: 

 Workingdays = calculate( sum(DimDate[Workingday]); DATESBETWEEN( DimDate[Date]; PutAwayHeaders[AssignmentDate];  PutAwayHeaders[CompleteDate])))

As you can see I named my date table DimDate. 

The last step is to create a measure with an average over this calculated column:

Average Workingdays = AVERAGE(Workingdays) 

[LewisH]

Workingdayss = CALCULATE(sum('Invoked Function'[WorkingDays]), DATESBETWEEN('Invoked Function'[Date], 'Put Away Headers'[Assignment_Date],'Put Away Headers'[Complete_Date]))

I've tried this but i'm getting this error

[Anonymous]

I think you accidentally created a measure instead of a column. Try again with a column using the same syntax (so right click on the table > new column). Then, create a measure with for Average Workingdays = AVERAGE(Workingdayss). 

[LewisH]

following your example I've created a table and populated workingday with either a 0 or 1. 

Secondly I'm trying to create a new column in that table called 

Workingdays = CALCULATE(sum('Invoked Function'[WorkingDay]), DATESBETWEEN('Invoked Function'[Date], 'Put Away Headers'[Assignment_Date],'Put Away Headers'[Complete_Date]))

I havn't done the measure yet but the new column isn't working 🙂

[Anonymous]

Can attach some print screens? A print screen of the field section (so I can see the types of columns of the tables) and two print sceens of the data view of these two tables. 

[LewisH]

[Anonymous]

Try to create the calculated column in the other table (PutAwayHeaders). It worked for me. I've also added +1 after the DATESBETWEEN because otherwise 1 workday doesn't get counted. 

[LewisH]

Thank you for your help.

How can i filter this so it doesn't factor in records which have a blank 'Put Away header'[Complete Date]

as this value is skewing the results, thanks

[Anonymous]

You're welcome. The easiest way probably is by adding another filter to the measure, such as: 

NOT(ISBLANK(Complete Date))

[LewisH]

So I have the table which specifies day of the week, how would i write a new column to do the 1 for weekday and 0 for weekend