Forum Discussion
Calculating the Average Days Difference
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)
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.
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
You're welcome. The easiest way probably is by adding another filter to the measure, such as:
NOT(ISBLANK(Complete Date))